Selecting a record before opening a form

[ManuelLavesa]

Hello,
I have a Form and a dialog box. The form consists of a Main Form and a Subform, Both have record sources from different queries and they are linked by a field named fkOrderID. The subform is a scrolling type. The main Form contains a field named InvoiceNumber.
The dialog Box consist of an Unbound combobox that has as record source the Orders Table, which stores the value for the field InvoiceNumber. Also it has a Button with the following code:

Private Sub EditSaleOrder_Click()
If IsNull(CboInvoiceNumber) Then
MsgBox ("Please Enter an Invoice Number from the List")
Exit Sub
End If
DoCmd.OpenForm "SalesOrderEntryFormEditing", acNormal, , "[InvoiceNumber]=" & Me.[CboInvoiceNumber], acFormEdit
DoCmd.Close acForm, Me.Name
End Sub

When you press the button it opens the form at the given Invoice value so that we can edit the record. This works fine as long as the original record has values in the form and the subform. However if for any reason the subform has no values then pressing the button opens a new record even though the combobox contains the Invoice Number that I want to edit and the Table Orders also contains the InvoiceNumber and parts of the Information from the Main Form. I am not sure what I am doing wrong. I would really appreciate it if you could help me. Thank you in advance for your time and efforts.

[RuralGuy]

Just for grins, leave off the last argument (acFormEdit). I believe it will work as you want.

[June7]

Each RecordSource property draws data from different queries and those queries are pulling from different tables?

Main form should be bound to parent table (such as Orders) and subform bound to dependent table (such as OrderDetails). Linking should be on primary key and foreign key fields. You say linking is on 'a field named fkOrderID' - what are the primary and foreign key field names?

[ManuelLavesa]

Hello, Thank you to both of you. I tried removing the acFormEdit, and it made no difference. About the form is linked by OrderID from table Orders (Master) and fkOrderID from table OrderDetails (subform). Somehow I think I should make the table OrderDetails accept Null values or something. Is there a trick to do that? Thank you.

[June7]

That sounds like a conventional arrangement. If you want to provide db for analysis, follow instructions at bottom of my post.

[Micron]

It reads like this Private Sub EditSaleOrder_Click() is on one form, and this CboInvoiceNumber (assuming "I have a dialog box" means it's a different form) is on another ( or the subform). If that's the case, If IsNull(CboInvoiceNumber) should raise an error. AFAIK, you cannot even refer to a main form control this way from a subform. You should get Data member not found, or unknown variable depending on how it's written.

[ManuelLavesa]

Hello I have good news I did solve the problem. It had to do with the record source of the main form that some how contained some data from the OrderDetails Table and was interfering with the record source of the subform. Now everything works. Thank you for your help and time.

[RuralGuy]

That's great! Thanks for the update.