Updating one combobox based on another combobox

[doobybug1]

I have 2 tables:

tblNotaries: NotaryRefNo (PK), NotaryName, NotarySurname, etc..

tblNotaryIndex: NotaryRefNo, Volume (Composite Key), etc...

I am trying to create a form where one selects the Notary name and surname being populated from tblNotaries and then the according volumes are shown to be selected from another combobox

I am trying this code on the afterUpdate event of the cmbNotary but is giving me a type mismatch. The NotaryRefNo is short text

Private Sub cmbNotary_AfterUpdate()
cmbVolume.RowSource = "SELECT Volume FROM tblNotaryIndex WHERE NotaryRefNo = " & cmbNotary.Column(0)
    cmbVolume.Requery
End Sub

 

[pbaldy]

Try

cmbVolume.RowSource = "SELECT Volume FROM tblNotaryIndex WHERE NotaryRefNo = '" & cmbNotary.Column(0) & "'"

 

[doobybug1]

Yes it worked fine...the single quotes are there to convert to string?

 

[pbaldy]

Text data type requires delimiters. If you hard-coded a parameter in design view of a query, you'd notice that Access adds delimiters. If you type Paul into the criteria, when you tab out it will be "Paul". In your code, if you hard-coded it you'd need:

cmbVolume.RowSource = "SELECT Volume FROM tblNotaryIndex WHERE NotaryRefNo = 'Whatever'"

 

[doobybug1]

So text fields also require single quote delimeters even if you are referring to their field name

 

[pbaldy]

Not sure what you mean by referring to their field name, but any time you're hard-coding the value, yes. I should clarify that if the value might contain an apostrophe, you'd need double quotes. More here on building SQL in VBA:

http://www.baldyweb.com/BuildSQL.htm