Subform/Parameter Passing Question 1

[ITProg]

I have 2 forms. The first form frmGetRecord has a combobox to get a patient number. This form also has a subform. The subform displays admission/discharge dates. There can be multiple admission/discharge records for a patient. I would like to select a record from the subform. When I click ok on this form, I would like my second form frmEvents to open, displaying the particular admission record selected for that patient. When frmEvents opens, it first calls the macro that opens frmGetRecord to retrieve th search information. A parameter query is then supposed to pull the information from the frmGetRecord. I'm not sure how to specify the particular record that is selected in the subform.

 

[TaintedFlames]

Hey ITProg,
If I understand well, you want a drop down list that well set a condition for your report? or query? Anyways, I'm pretty sure you can do what you want with "DoCmd.Openform" or Open*something*. You should look at the parameters "filter" and "WhereCondition".

Hope This helps.


Jay

 

[ITProg]

I am using the DoCmd.Openform to open another form based on the values in a subform. I have the string variable below to place in the where criteria of the of DoCmd. Two of the fields I need to reference are on the subform. I get an error message that Access can't find the field frmSubGetRecord referred to in your expression. I am having trouble referencing the two fields on the subform. Can someone tell me what I might be doing wrong?

stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" _
And "[AdmitDate]=" & "'" & Forms!frmGetRecord! frmSubGetRecord![AdmitDate] & "'" _
And "[DischDate]=" & "'" & Forms!frmGetRecord!frmSubGetRecord![DischDate] & "'"

DoCmd.OpenForm stDocName, , qryAdmissions, stLinkCriteria, acFormEdit

 

[ITProg]

I have changed the string to below and now receive a type mismatch error when I try to run this.

stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" _
And "[AdmitDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "#" _
And "[DischDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#

 

[lespaul]

Is [CNo] a number? then you don't need the quotes around it.


Leslie

 

[ITProg]

[CNO] is a text field.

 

[cmmrfrds]

After you build the string stLinkCriteria, then display it in the immediate window to make sure it got built correctly.

Debug.Print stLinkCriteria

Do Control G to open immediate window after running the code.

 

[ITProg]

If I break the string stLinkCriteria into separate pieces to test each value, the immediate window displays the correct value. When I put the whole string together, nothing shows in the immediate window and I get the type mismatch error.

 

[dhookom]

Your And must be inside the quotes
stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" _
" And [AdmitDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "#" _
" And [DischDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#"

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]

 

[ITProg]

When I put the AND inside quotes, I get a compile error, Expected: end of statement

 

[dhookom]

I would get rid of some of the extra quotes and stuff and use:

stLinkCriteria = "[CNo]=""" & Me![cmbCNO] & """ AND [AdmitDate]= #" & _
Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "# AND [DischDate]= #" & _
Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#"

debug.Print stLinkCriteria

If this doesn't seem to work, come back with your exact attempt to do this as well as the value in your debug window.

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]

 

[ITProg]

I tried your suggestion with the code below:

stLinkCriteria = "[CNo]=""" & Me![cmbCNO] & """ AND [AdmitDate]= #" & _
Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "# AND [DischDate]= #" & _
Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#"

This is what I got in the immediate window:

[CNo]="12345" AND [AdmitDate]= #4/16/2004# AND [DischDate]= ##

I got an error message:
syntax error in date in query expression [CNo]="31950" AND [AdmitDate]= #4/16/2004# AND [DischDate]= ##[CNo]="31950" AND [AdmitDate]= #4/16/2004# AND [DischDate]= ##

 

[lespaul]

what DischDate are you searching for?

AND [DischDate]= ##

Leslie

 

[cmmrfrds]

You are getting a NULL returned in this field
Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate]

So, investigate why that is the case.

 

[dhookom]

I would create code like:

stLinkCriteria = "1=1 "
If not IsNull(Me.cbmCNO) Then
   stLinkCriteria = stLinkCriteria & _
       "AND [CNo]=""" & Me![cmbCNO] & """ "
End If
If Not IsNull(Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate]) Then
   stLinkCriteria = stLinkCriteria & _
     " AND [AdmitDate]= #" & _
     Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "# "
End If
If Not IsNull(Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate]) Then
   stLinkCriteria = stLinkCriteria & _
     "AND [DischDate]= #" & _
     Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#"
End If

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]

 

[ITProg]

Thanks, Duane. That worked great!!