problem with seeing records in subform

[mrbboy]

I have two tables:

tbl_Audit
AuditID <PK>
Auditor Name
etc

tbl_AuditDetails
DetailsID <PK>
AuditID <FK>

I have a form, frm_Audit, that is used to enter general info into tbl_Audit. This form also contains a subform, frm_AuditDetails, and this is used to enter the specifics of an audit. The Link Child and Master fileds are AuditID. I have another form, frm_Metrix, that is used to view all the items (by DetailsID) for each audit (AuditID). I placed this code in the double click event of the text box txt_DetailsID but everytime the text box is double clicked, a blank form, frm_Audit, opens.

DoCmd.OpenForm "frm_Audit", , , "forms!frm_Audit!frm_AuditDetails.Form!DetailsID = '" & Me.txt_DetailsID & "'"

What am I doing wrong?

 

[JBinQLD]

Two ideas mate:

1) Is the record saved to the table before the user double clicks? To be double sure try on an old record.

2) I think you have a typo?:

DoCmd.OpenForm "[red]frm_Audit[/red]frm_Metrix", , , "forms!frm_Audit!frm_AuditDetails.Form!DetailsID = '" & Me.txt_DetailsID & "'"

JB

 

[JBinQLD]

Three ideas mate:

1) Is the record saved to the table before the user double clicks? To be double sure try on an old record.

2) I think you have a typo?:

DoCmd.OpenForm "[red]frm_Audit[/red]frm_Metrix", , , "forms!frm_Audit!frm_AuditDetails.Form!DetailsID = '" & Me.txt_DetailsID & "'"

3) If DetailsID is a number field then remove the single quotes thus:

DoCmd.OpenForm "frm_Audit", , , "forms!frm_Audit!frm_AuditDetails.Form!DetailsID = [red]" & Me.txt_DetailsID[/red]

JB

 

[JBinQLD]

4 little things, maybe useful, maybe not!

1) Is the record saved beofre the user double clicks? Try going to an old record incase and see if that works

2) Typo?:

DoCmd.OpenForm "[red]frm_Audit[/red]frm_Metrix", , , "forms!frm_Audit!frm_AuditDetails.Form!DetailsID = '" & Me.txt_DetailsID & "'"

3)The where clause should be SQL so you refer to the field name, not the control:

DoCmd.OpenForm "frm_Audit", , , "DetailsID = '" & Me.txt_DetailsID & "'"

4) If DetailsID is an autonumber/integer then remove quotes:

DoCmd.OpenForm "frm_Audit", , , "DetailsID = [red]" & Me!txt_DetailsID[/red]

Hope this helps,

JB

 

[JBinQLD]

Sorry for above, I'm not sure how they published while i was editting

JB

 

[mrbboy]

Thanks for the reply. Just looking at the code, will this work even if [DetailsID] is in the subform, and not in the main form, frm_Audit?

 

[JBinQLD]

Please clarify - you say frm_audit is based on tbl_Audit. Tbl_Audit DOES NOT contain the field "DetailsID" so you cannot open that form based on DetailsID. This is why I wondered if you meant form frm_Metrix.

Remember, each form is based on a table or query. Each query or table of course has fields. The where clause on the openform command is for you to specify in SQL a criteria. So,

"[FieldNameInFormBeingOpenedRecordSource]= " & forms!AnyForm!AnyControl

or

"[FieldNameInFormBeingOpenedRecordSource]= " & forms!AnyForm!SubformName.form!AnySubformControl

Of course, if your control contains text then you delimit thus:

[field]= '" & forms!...!control & "'"

date:

[field]= #" & forms!...!control & "#"

Hopefully this clarifies things a bit

JB