Subform not displaying correct record when main opened using DLookup

[Robbo1974]

Hi all,

I am having a problem moving to the correct subform record when opening the main form using a DLookup routine. What I want to do is open a form which holds client details which has a subform which displays order details.

The user enters an order number into a search form, clicks a button and my mainform opens at the client record which owns that unique order number and the subform displays the order record defined by that order number.

The code below opens the main form fine, but does not move to the correct subform record. Any ideas what's wrong with my code?

txtOrderNum is the field on my search form, frmFindOrder frmContactInformation is the main form and frmOrders1subform is the order subform

Public Sub Command0_Click()

On Error GoTo err_command0_click

Dim intCustomer As Integer
Dim intOrderNum As Integer

intOrderNum = Me.txtOrderNum

intCustomer = DLookup("CustomerID", "tblOrders1", "OrderNumber=" & intOrderNum)

DoCmd.OpenForm "frmContactInformation", , , "[CustomerID]=" & intCustomer

DoCmd.Close acForm, "frmFindOrder"

Forms![frmContactInformation].frmOrders1subform.RecordsetClone.FindFirst "[OrderNumber]=" & intOrderNum

Forms![frmContactInformation].frmOrders1subform.Bookmark = Forms![frmContactInformation].frmOrders1subform.RecordsetClone.Bookmark

GoTo Exit_command0_click

err_command0_click:

'err 94 is improper use of null value
If Err.Number = 94 Then

MsgBox "There is no order corresponding to the number you have entered." & vbCrLf & vbCrLf & "Please check and try again.", vbExclamation, "Order Number Not Found"

Me!txtOrderNum.Value = null

Me!txtOrderNum.SetFocus

Else:

Exit Sub

End If

cheers! Robbo ;-)

 

[Bammer]

Is there any reason you don't use the normal way of linking main forms to a subform by using the Link Child Field
Link Master Field properties?

 

[Robbo1974]

My link field is already defined as [CustomerID]. This means that my subform displays all of the records from tblOrders where [CustomerID] = mainform![CustomerID] (mainform is based on a seperate table, tblContactInformation).

What I need to do is open the main form at the client record which 'owns' the order whose ordernumber has been entered into my search form (intOrderNum) and then move through the records on the subform to the record where [OrderNumber] = intOrderNum. This is proving to be a right pain in the a**e. In fact I think I'll go get a job in a factory. ;-) Robbo ;-)

 

[JaneC]

Robbo,

Sorry I cannot get this code to work at all on my database. I don't usually use recordsetclones !

However, you might want to change the bit where it searches for the order number as follows :

DoCmd.Close acForm, "frmFindOrder"

Forms![frmContactInformation].frmOrders1subform.RecordsetClone.FindFirst "[OrderNumber]= '" & intOrderNum & "'"

as this is a common syntax error.

Please let me know if this works !

Jane

 

[Robbo1974]

Unfortunately not Jane, thanks for trying though! Perhaps these responses will push me a bit further up the list too! Robbo ;-)