Pass from submform

[Newbie11]

I have a Customer Records Subform that displays specifc customer records on the Main Customer Form. I have an Edit Order button that I would like to open up the the Customer Records Form to edit an order.



I want the form to open to whatever record is selected in the datasheet. Currently it is always opened to the last record. Is there any way to open to the selected record?

[June7]

So you select record in the subform and then click button to open a form? You can do edits in the subform?

I use VBA to open form filtered by the WHERE CONDITION of DoCmd.OpenForm. I always name the subform container control different from the object it holds, like ctrOrder.

DoCmd.OpenForm "Customer Records Form", , , "OrderID=" & Me.ctrOrder!OrderID, , acDialog

Then might need to requery the subform after the form closes so new record will show.
Me.ctrOrder.Requery

[Newbie11]

Thanks for the solution!!

I came across another issue. How can I eliminate the creation of another record where the first name and last name already exist? That is I want to prevent users from creating dupliate files and possibly show a message box that the cusotmer already exists.

[June7]

You can have code search the data base for the names. But this won't take into account misspelling by user. Better to restrict user selection. Have a combobox with a RowSource that is a query with fields for CustomerID and LastName,FirstName (concatenated as one value). As user types lastname,firstname the combobox will match to items in list. If no match offer user option to enter a new record. This can be handled with the NotInList event of the combobox. http://www.everythingaccess.com/tuto...-lookup-tables

You can also have a search capability built on form. Refer to tutorials at http://datapigtechnologies.com/AccessMain.htm especially Creating a Basic Search Form in the Access Forms: Tips and Techniques section.

[Newbie11]

I actually wanted to try something a bit simpler like a DLookup to see if the ID already exists.

I have the following code:

If DLookup(ID) = 1 Then
MsgBox "Patient already exists"
Else ......
End if

However I keep geting a complier error: argument not optional on the DLookup line.

[June7]

Need to specify the table to search. Literal strings must be in quotes. If search is no match the return is null.
If Not IsNull(DLookup("ID", "yourtablename", "ID=1")) Then

Refer to Access Help about DLookup.