MS Access VBA issue

Question

I'm making a report in MS Access - what I'm trying to do here is basically APPEND a query to a table that I've already created - I select the first value, change it and update the table. The issue that I'm coming across is - this report will be used by a VB6 application. So the user won't be seeing Access at all.

The thing with my append query is that it needs a USER ID to run (4 digit number). Normally when I run a report in Access I pass the parameters to a form in Access - and I use them to run queries. However, in this case, I need the user to enter a value when appending the query, additionally, when appending a query in VBA it first says "You are about to append a query, are you sure" (or something along those lines), so is there a way to automate that as well, so when I append it nothing happens?

Here is my code for appending and selecting date from the tempTable:

CurrentDb.Execute "DELETE from [tempCompanyMgmt-NOW];"
DoCmd.OpenQuery "qryCompanyMgmt-SUE" - i made this append!
Set rs1 = CurrentDb.OpenRecordset("Select * from [tempCompanyMgmt-NOW]", , dbOpenDynamic)

So as long as I press OK, YES when I get notified of the APPEND process and enter the parameter for USER ID - everything works fine.

Answer 1

Looks like a typo in your markdown, should the 2nd line be:

DoCmd.OpenQuery "qryCompanyMgmt-SUE - i made this append!"

You'll need to remove the reference to the form inside the qryCompanyMgmt-SUE - i made this append! query, and swap it for a parameter name. You can use the Access interface to explicitly add a parameters clause to the query, and then using ADO (or DAO) from VB6, set a parameter value before you open/execute the query.

The "You are about to append a query, are you sure" message is an Access feature (and it can be disabled), so if you want the VB6 application to provide such a warning, then you'll need to create it yourself with a MsgBox.

Answer 2

One option would by putting your append query into the code and filling in the parameter that way.

I don't know your exact scenario, but something like:

If not isValidUserID(me.UserID) Then
    msgbox "Please enter a a valid user id"
    exit sub
End If



Dim strSQL As String
strSQL = "DELETE * from [tempCompanyMgmt-NOW];"
CurrentDb.Execute strSQL, dbFailOnError


strSQL = "INSERT INTO tempCompanyMgmt-NOW ( FieldName1, FieldName2, FieldName3 ) " & _
        "SELECT FieldName1, FieldName2, FieldName3 FROM tempCompanyMgmt WHERE UseriD=" & Me.UserID

CurrentDb.Execute strSQL, dbFailOnError

To validate the user id you could do something like:

If (Len(me.UserID) = 4 And IsNumeric(me.UserID)) Then

or

Public Function isValidUserID(varUserID As Variant) As Boolean
Dim blnRet As Boolean

If Len(varUserID) = 4 And IsNumeric(varUserID) Then
    blnRet = True
End If

isValidUserID = blnRet
End Function

Answer 3

To get rid of the MsgBox telling me I'm about to append a query i included this in my module before I open my append query..

DoCmd.SetWarnings False

And I realized once I have the value passed to the form (userID), that value gets passed on as a parameter when my query gets appended. So it's all set. Thanks for all help!