Conditional Combobox

[gmenon100 via AccessMonster.com]

I am using MS access project with SQL backend.
I have a main form (1) which has 2 unbound combobox (vendor & invno).

I have been able to populate the 2nd combobox using the below statement:

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

this populates all "Titles" for vendors selected in Combobox 1. However, I
want it to add one more condition. I want it to also check for status
<>"PAID". Status, Vendor are fields in a table.

In short if a vendor is selected in combobox1, combobox2 should display only
open invoice numbers.

Can someone please help me formulate this. Sincerly appreciate response.

Gmenon

 

[Douglas J. Steele]

Presumably you mean

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

because what you have is invalid.

To add a second condition, use

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" AND Status <> 'Paid' "
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

 

[gmenon100 via AccessMonster.com]

Hi Douglas,

Yes you were right, the code i use was invalid. I used the codes given by you
and it worked. Thanks for your help and response.

Gmenon

Presumably you mean

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

because what you have is invalid.

To add a second condition, use

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" AND Status <> 'Paid' "
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

I am using MS access project with SQL backend.
I have a main form (1) which has 2 unbound combobox (vendor & invno).

[quoted text clipped - 19 lines]

Gmenon

 

[gmenon100 via AccessMonster.com]

Hi douglas,

I have another question, if you can help. I want to use a formula in a query
in a access project as under:

if [invval] *.0125 > 100 then invval*.0125 else 100 alias [NEGOCHG]

WHERE [INVVAL] AND [NEGOCHG ] are fields in a table and I am running a query
on the table. FYI [INVVAL] is a calculated value derived from [INVQTY] *
[INVPRICE].

I dont know if I am clear in my question or am I confusing.

Presumably you mean

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

because what you have is invalid.

To add a second condition, use

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" AND Status <> 'Paid' "
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

I am using MS access project with SQL backend.
I have a main form (1) which has 2 unbound combobox (vendor & invno).

[quoted text clipped - 19 lines]

Gmenon

 

[Douglas J. Steele]

Are you querying a table that's linked to SQL, or are you running a
pass-through query?

If it's against a linked table, try

IIf([invval] *.0125 > 100, invval*.0125, 100) AS [NEGOCHG]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi douglas,

I have another question, if you can help. I want to use a formula in a
query
in a access project as under:

if [invval] *.0125 > 100 then invval*.0125 else 100 alias [NEGOCHG]

WHERE [INVVAL] AND [NEGOCHG ] are fields in a table and I am running a
query
on the table. FYI [INVVAL] is a calculated value derived from [INVQTY] *
[INVPRICE].

I dont know if I am clear in my question or am I confusing.

Presumably you mean

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

because what you have is invalid.

To add a second condition, use

Private Sub vendor_AfterUpdate()
Me.invno.RowSource = "SELECT title FROM" & _
" [issue totals query] WHERE vendor = " & Me.vendor & _
" AND Status <> 'Paid' "
" ORDER BY title"
Me.invno = Me.invno.ItemData(0)

End Sub

I am using MS access project with SQL backend.
I have a main form (1) which has 2 unbound combobox (vendor & invno).

[quoted text clipped - 19 lines]

Gmenon