Link to home
Start Free TrialLog in
Avatar of Fred Fisher
Fred FisherFlag for United States of America

asked on

Unable to get DoCmd.OpenForm "frmInstrumentServiceRecords" to work.

I have a form frmInstruments that lists a number of different instruments in the table tblInstruments. Located on frmInstruments is a command button btnAddEditMaintenanceHistory that when clicked I would like it to open frmInstrumentServiceRecords only for the the instrument currently displayed in frmInstruments.  Seemed easy enough but after two days I am stumped.  Below is the code for the click event for btnAddEditMaintenanceHistory.

Private Sub btnAddEditMaintenanceHistory_Click()
     Dim vInstrumentServiced As String
     Dim vSQL As String        
     vInstrumentServiced = Me.InstModel
     vSQL = "[ServInstModel] = " & vInstrumentServiced
     DoCmd.OpenForm "frmInstrumentServiceRecords", , , vSQL
End Sub

Me.InstModel is the Instrument field name on frmInstruments.  ServInstModel is the field name on frmInstrumentServiceRecords that needs to be = to Me.InstModel.

When I run this I get a runtime error 3075 "Syntax error (missing operator) in query expression 'ServInstModel = Ampico B Mason & Hamlin 5'8" Grand.

Any thoughts?



Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Try doubling the doube-quote in Ampico B Mason & Hamlin 5'8" Grand:

vSQL = "[ServInstModel] = " & Replace(vInstrumentServiced, """", """""")

Open in new window

Avatar of Fred Fisher
Fred Fisher
Flag of United States of America image

ASKER

Open in new window

Upon further review I have several issues in my tblInstruments.  That is Ampico Mason & Hamlin 5'8" Grand is three pieces of information so I will need to split that up into Instrument Name (Ampico B Mason & Hamlin), Instrument Type (Grand Piano), and Instrument Dimensions.
That I can handle.

In the meantime I changed Ampico Mason & Hamlin 5'8" Grand to Ampico B Mason & Hamlin.  Now when I run
vSQL = "[ServInstModel] = " & Replace(vInstrumentServiced, """", """""")

Open in new window

This returns:  vSQL = "[ServInstModel] = Ampico B Mason & Hamlin"

The DoCmd.OpenForm "frmInstrumentServiceRecords", , , vSQL results in a slightly different error
 
"Runtime error 3075 "Syntax error (missing operator) in query expression "[ServInstModel] = Ampico B Mason & Hamlin".


Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Yes, the single-quotes were missing:

vSQL = "[ServInstModel] = '" & Replace(vInstrumentServiced, """", """""") & "'"

Open in new window

Avatar of Fred Fisher
Fred Fisher
Flag of United States of America image

ASKER

Ok, now I get the error Runtime error '3464' Data type mismatch in criteria expression.  Now I see that why this has been such a bear.  ServInstModel is a number field while InstModel is a text field.  Is User generated imagethere a better way to do this?
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Yes. You'll need a field in the service table to match InstrumentID (Long) to hold the foreign key.
Avatar of Fred Fisher
Fred Fisher
Flag of United States of America image

ASKER

Isn't the foreign key ServiceID?

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
From the names of the fields: No. Should be ServiceInstID or similar.

But I don't have your tables ... you do.
Avatar of Fred Fisher
Fred Fisher
Flag of United States of America image

ASKER

I have stripped the Instrument Database from the Music Manager database and it is attached.Instrument.accdb

ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial