How to populate PK in called form

[davidfaith101]

Hi,

I'm using one form (lookup) to call another (customer).

I set the properties on the command button object on the lookup form to open the customer form with the contents of a text field (phone number) in the lookup form matching the primary key of the customer form.

If the record exists in the customer form it opens with the record populated.
which is great! But...

If the record does not exist I get a blank customer form. Does anyone know of a way that I can automatically populate the primary key field on the called form from a calling form so that I dont have to type in the lookup text (the phone number) in the customer form when it is a new record? In other words if there is no customer in the database with a particualar phone number I would like the customer form to be pre-populated with the phone number already typed in the form. Is this possible?

Thanks,
David

 

[davidfaith101]

Answer: How to Populate PK in called form

I tried this and it worked!

Step 1. put a button on the calling form (customerLookup) and place the following code in the OnClick Event properties

Private Sub CustomerLookup_Click()
On Error GoTo Err_CustomerLookup_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Customer"

stLinkCriteria = "[CustomerID]=" & "'" & Me![Text1] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_CustomerLookup_Click:
Exit Sub

Err_CustomerLookup_Click:
MsgBox Err.Description
Resume Exit_CustomerLookup_Click

End Sub

(Note: Step 1 can be automated by the wizard if done when the button is first created)

Step 2. put the following code just before Exit_CustomerLookup_Click


If IsNull(Form_Customer.CustomerID) Then
Form_Customer.CustomerID = Me.Text1
End If


Step 3. In the called form, add the following code to the On_load event
to move the cursor to the first empty field (in my case it was CustomerName)

If IsNull(Me.CustomerName) Then

DoCmd.GoToControl "CustomerName"
End If


It works, but seems like a bit of a kludge. Anyone else have a better way to do it?

Thanks,
David

 

[VicRauch]

Yes, it is possible.
In your OnCurrent event of the form, you will need two things. First, have an indicator that shows if this is the first record this form has displayed for this time of being opened. You will only want the phone number defaulted into the phone number field for the first record display (including only the first blank record). Then, you can test for first record display (or no records displayed before, which ever wording you would like best), then test if this is a new record (easiest way is if Phone Number is Null). If both of these are true, then do a Me!PhoneNumber = Forms!frmCallingForm!PhoneNumber.

Hope that is clear enough to follow. If not, let me know and I will try to make it clearer.

 

[VicRauch]

David, Looks like we were both entering a reply at the same time. Hope mine is able to help you some.