problem with combo box synch

[sobeit via AccessMonster.com]

i created a sync combo box copying from a sample database

but the problem is i cannot get the right criteria for the second cbo
because the format of the first cbo is like "09-001" the result of the query
on my second combo only reflects as 9-1

ihave used the format function
but cannot get the right result

Private Sub cboSONUMBER_AfterUpdate()
Dim sitemSource As String

sitemSource = "SELECT [MONITORING].[SO], [MONITORING].[PROJECT],
[MONITORING].[REMARKS] " & _
"FROM MONITORING " & _
"WHERE [SO] = " & Format(Forms![CBOFILTER]!
cboSONUMBER, "00-000")
Me.cboitemdesc.RowSource = sitemSource
Me.cboitemdesc.Requery

please help

 

[Jeanette Cunningham]

Hi sobeit,

Try it like this:

Private Sub cboSONUMBER_AfterUpdate()
Dim sitemSource As String

sitemSource = "SELECT [MONITORING].[SO], [MONITORING].[PROJECT],
[MONITORING].[REMARKS] " & _
"FROM MONITORING " & _
"WHERE [SO] = """ & Forms![CBOFILTER]!
cboSONUMBER & """"
Me.cboitemdesc.RowSource = sitemSource
Me.cboitemdesc.Requery

Quotes:
that is 3 double quotes before & Forms and 4 double quotes after SONumber &

Explain:
"09-001" is not a number, but a word as far as Access and jet are
concerned.
For numbers you can go "[SO] = " & Me.cboSONumber

For words - string data type - you need more quotes
"[SO] = """ & Me.cboSONumber & """"


The format function always gives you a string data type.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[sobeit via AccessMonster.com]

thank you jeanette for the quick response

but still cannot get the right result

if the value of cboSONUMBER = "09-001" it reflects on the criteria on the
cboitemdesc as "9001"

Hi sobeit,

Try it like this:

Private Sub cboSONUMBER_AfterUpdate()
Dim sitemSource As String

sitemSource = "SELECT [MONITORING].[SO], [MONITORING].[PROJECT],
[MONITORING].[REMARKS] " & _
"FROM MONITORING " & _
"WHERE [SO] = """ & Forms![CBOFILTER]!
cboSONUMBER & """"
Me.cboitemdesc.RowSource = sitemSource
Me.cboitemdesc.Requery

Quotes:
that is 3 double quotes before & Forms and 4 double quotes after SONumber &

Explain:
"09-001" is not a number, but a word as far as Access and jet are
concerned.
For numbers you can go "[SO] = " & Me.cboSONumber

For words - string data type - you need more quotes
"[SO] = """ & Me.cboSONumber & """"

The format function always gives you a string data type.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

i created a sync combo box copying from a sample database

[quoted text clipped - 18 lines]

please help

 

[Jeanette Cunningham]

This could be a problem with data types.
Going to the table that has a field called SONumber, what data type is the
SONumber field?
Then to the table called Monitoring, what data type is SO?
On to cboSONumber - how many columns does it have?
Is the value "09-001" in the first column of the combo, or is it in the
first column that you can see, the first column of a combo is often hidden.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

thank you jeanette for the quick response

but still cannot get the right result

if the value of cboSONUMBER = "09-001" it reflects on the criteria on the
cboitemdesc as "9001"

Hi sobeit,

Try it like this:

Private Sub cboSONUMBER_AfterUpdate()
Dim sitemSource As String

sitemSource = "SELECT [MONITORING].[SO], [MONITORING].[PROJECT],
[MONITORING].[REMARKS] " & _
"FROM MONITORING " & _
"WHERE [SO] = """ & Forms![CBOFILTER]!
cboSONUMBER & """"
Me.cboitemdesc.RowSource = sitemSource
Me.cboitemdesc.Requery

Quotes:
that is 3 double quotes before & Forms and 4 double quotes after SONumber
&

Explain:
"09-001" is not a number, but a word as far as Access and jet are
concerned.
For numbers you can go "[SO] = " & Me.cboSONumber

For words - string data type - you need more quotes
"[SO] = """ & Me.cboSONumber & """"

The format function always gives you a string data type.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

i created a sync combo box copying from a sample database

[quoted text clipped - 18 lines]

please help

 

[sobeit via AccessMonster.com]

now i see whats wrong with it

THANK YOU Miss Jeanette

This could be a problem with data types.
Going to the table that has a field called SONumber, what data type is the
SONumber field?
Then to the table called Monitoring, what data type is SO?
On to cboSONumber - how many columns does it have?
Is the value "09-001" in the first column of the combo, or is it in the
first column that you can see, the first column of a combo is often hidden.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

thank you jeanette for the quick response

[quoted text clipped - 39 lines]