DoCmd.OpenForm Troubles

[itprog]

I have a form(frmGetRecord) with a subform(frmSubGetRecord). frmGetRecord has a combobox cmbCNO to choose the patient. The subform displays the admit date and discharge date(if there is one). There can be multiple admit/Discharge dates for a patient. Based on the values from fields on the form and subform, I would like to open a data entry form(frmEvents) for the particular record. I have a command button to run the code to open frmEvents. I am having trouble with the criteria in the where condition.

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


DoCmd.OpenForm stDocName, , stQryName, stLinkCriteria, acFormEdit

When I click on the command button I get a type mismatch error. If I test the StLinkCriteria separately, the DoCmd.OpenForm will work for just the CNO field or just the AdmitDate field. If I test those two strings together, I get the type mismatch. Also, DischDate could be blank and I'm not sure how to add an IIf statement to the string to test for that in the where statement. I'm also not sure how to make sure it picks the correct record if there is more than one admit date. I would like to select the desired date record, then have the command button open the appropriate record.

Hope this makes sense. Thanks for any help.

 

[Uncle Gizmo]

>>>> stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" <<<<

Does this mean you are trying to specify two different criteria at the same time? If that is the case, well you can't!

 

[Uncle Gizmo]

>>>> subform displays the admit date and discharge date <<<<<

If you amend the table where the subform stores its information, so that it has a unique ID field, then you will be able to select the particular row on the subform, and then have a command button to load a form to edit the details of that row. is that what you are looking for?

 

[itprog]

>>>> stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" <<<<

Does this mean you are trying to specify two different criteria at the same time? If that is the case, well you can't!

I am comparing the value in [CNO] (which is in qryAdmissions) to the value in the combobox field on the form (the same for AdmitDate and DischDate). I want to display the record that has the CNO, admitdate and dischdate shown on frmGetRecord in the form frmEvents - which will allow for further data entry for this chosen record.

 

[Uncle Gizmo]

>>>>> I would like to open a data entry form(frmEvents) for the particular record <<<<<

And what "single" Criteria Identifies this row?

Does the row you want to open the form for, (and it won't be a data entry form Will it? it is for editing existing records No?) have a unique single criteria to identify it to your string link criteria?

It appears to me that you are trying to Open the form with more than one criteria, please correct me if I am wrong.

Please keep it simple because I am having a job to understand what you are trying to do.

 

[Rich]

Surely they can only have one unique admission date, or can the be admitted on the same day more than once?

 

[itprog]

They can be readmitted at a later date during the year.

 

[Rich]

yes but according to your post you're looking for a specific admission date

 

[itprog]

I would like to select an admission date from the display in the subform. After I select the admission date from the subform, I would like to open another form displaying that entire record.

 

[Rich]

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

 

[Uncle Gizmo]

Well I'm damned! I owe you an apology! I didn't realize that you could pass two or more parameters in the "stLinkCriteria".

I am usually very careful and say "I don't think you can" because it's amazing how many times you don't think something can be done, and some clever sod comes along with a way of doing it. However in this case I said "you can't" I suppose that was asking for trouble!

I have attached a small sample database, to demonstrate this.

And again, please Accept my apology for being well I'll leave you to you to fill in the blank.