Change forms without changing records

[ProAdjuster]

I have one form, which I can use to look up a record.

The way this works is that the form is linked to a table called contacttempstore. It has a lookup field to my main table, and a field that stores the contact number in the only cell of contacttempstore. Every time I change the lookup field (ie. select a new name from the drop-down), the table requeries to pull up the contact I have selected. The subform is linked to the main form, so I can always access the record.

On this contacttempstore form, I have a button that I can use to open just the subform, so that I can add records, or delete records.

The problem I am experiencing is, when I switch to just the subform, I would like it to stay on the same record. Is there a way I can use a macro or code to look up the contacttempstore table and find the matching record?

 

[dhookom]

Doesn't the command button wizard have an option within the Open Form method of filtering the new form based on a value from the current form?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ProAdjuster]

I have tried, but within the wizard, it isn't working.

I tried to write the code, but I don't know enough about coding to make it work.

 

[ProAdjuster]

Does anyone have any idea?

I've tried putting this into a macro:

Goto
where "contact.contact=contacttempstore.contact

 

[dhookom]

I assume you want to open a form and display the record matching the selection in your combo box. I would use code like this:

Dim strWhere as String
Dim strFormName as String
strFormName = "frmYourFormName"
If Not IsNull(Me.cboYourComboBoxName) Then
    'assuming your field is text
    strWhere = "[Your Field Name] =""" & _ 
       Me.cboYourComboBoxName & """"
End If
DoCmd.OpenForm strFormName, , , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]