HOW - Open form to record created by query!?

[nonturbo]

I have a command button on a form that basically copies the current record from a contacts table over to a customer table using an append query. After creating the record, I'd really like for access to open the customers form to that record, but can't seem to get it to work. Here's my code I'm using, which works fine except it opens the customers form to the first record.. not the one created by the query. Any help would really help! Thanks In Advance..


Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCreateCustomer&quot
qdf.Parameters("[paramContactID]&quot = Me.ContactID
qdf.Execute

Set qdf = Nothing
Set db = Nothing

MsgBox "Customer Created Successfully!", vbOKOnly

DoCmd.OpenForm "Orders by Customer", , , , acEdit

 

[rbowes]

From what I can see, you need to establish criteria for the form's record source. If the form is based on qryCustomers (which returns all of the records in the tblCustomers), the way you are opening the form tells Access that you want all of the records returned. Set the whereCondition to the customer's CustomerID or and you'll get what your looking for.

I might do this:

Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCreateCustomer&quot
qdf.Parameters("[paramContactID]&quot = Me.ContactID
qdf.Execute

Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCreateCustomer&quot
qdf.Parameters("[paramContactID]&quot = Me.ContactID
qdf.Execute

Set qdf = Nothing
Set db = Nothing

MsgBox "Customer Created Successfully!", vbOKOnly
DoCmd.OpenForm "Orders by Customer", , , "CustomerID =" & Me.ContactID, acFormEdit


Of course, this assumes that you are setting the CustomerID to the ContactID. If not, you will have to run a routine that returns the CustomerID (like db.openrecordset) to find the CustomerID first.

Hope that helps!

BB