Access append query won't work on form

[RPGguy]

I have an append query that uses 2 variables found on a form. When I run the query by itself it asks for the variable values and works fine. When I run it in my form nothing happens. Since the query works I won't show that but here is the subroutine that executes it:

Private Sub cboSystemCopy_AfterUpdate()

Dim stQryName As String

If cboSystemCopy > "" And Forms!frmSystem!System > "" Then
stQryName = "qryCopySystem"
DoCmd.OpenQuery stQryName
cboSystemCopy = ""
End If

End Sub

The field System (let's call it A) is the name of a new computer system being entered on the form. cboSystemCopy (let's call it B) is the name of an existing computer system selected from a combo box. The query copies all parts in a table from B to A.

As I said, it works if I run the query alone and enter the parameter values.

Thanks for everyone's help.

 

[KenReay]

Hi

How have you specified the Variables in the query?

To run the query in the way you are trying to do so, you need to specify them as refering to the form fields, eg Forms!YourForm!cboYourCombo.

Alternatively you could use paramter statements within your SQL string, and then evaluate populate the parameters collection of the querydef and execute the querydef:

Dim qdf As QueryDef
Dim Db as Database

Set Db = CurrentDb()
Set qdf = Db.QueryDefs("YourQuery&quot
qdf.Paramters("YourParameter&quot = cboYourCombo
qdf.execute

above is just to illustrate the idea, you need to use your object names etc

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[KenReay]

Hi

OOPS!

Should be

qdf.Parameters("YourParameter&quot = cboYourCombo

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use