Auto-Populate field from command button

[esskaykay]

I have a query (qryRequests) linking two tables (tblPermits and tblRequests) by the field called Permits_No, with all records from tblRequests and only match records from tblPermits.

I have a form (frmPermits) with a command button that opens a second form (frmRequests) in ADD mode. The Permit_No field is in both forms. Permit_No in tblPermits is populated. When I click the button on frmPermits that opens frmRequests, I would like the Permit_No field to be populated with the same entry as in tblPermits (as displays in frmPermits). Everything seems to be working fine except for the auto-populate function.


I tried a DLookup function but I’m sure I have the wrong syntax. On the “On Click” property I coded:

DLookup("[Permit_No]", "tblPermits", "[Permit_No] = [Permit_No]”) = Me.Permit_No

Obviously this was wrong so I tried hard coding in a permit number (08-60).

DLookup("[Permit_No]", "tblPermits", [Permit_No] = ‘08-60”) = Me.Permit_No

This too failed. Any suggestions would be greatly appreciated.

Thanks,
SKK

 

[pbaldy]

Here's a good reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

It appears you may be reversing your logic, as it's:

Target = Source

to set a value.

 

[esskaykay]

Thanks - I like this. I tried all but still am having problems. One thing I did was create an append query using the existing data as a condition. It almost works. What I'm seeing is on my ADD form is in order for the query to update, I must first commit the record by advancing to the next new record (via the scroll button on the mouse). Is there code that in essence does this for me? What I would do is code this action prior to running my append query.

Thanks again,
SKK

 

[pbaldy]

To force the record to save?

DoCmd.RunCommand acCmdSaveRecord

 

[esskaykay]

That did it -- I think I'm on to something.

Thank you,
SKK

 

[pbaldy]

No problemo!