Why is this an error?

[ryan.fitzpatrick3]

What's wrong with this, I get an error here.

Private Sub Form_Open(Cancel As Integer)
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name" & " From
tblAdageVolumeSpend Order By "" & " "tblAdageVolumeSpend.en_vend_key;"
End Sub

 

[Dirk Goldgar]

What's wrong with this, I get an error here.

Private Sub Form_Open(Cancel As Integer)
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name" & " From
tblAdageVolumeSpend Order By "" & " "tblAdageVolumeSpend.en_vend_key;"
End Sub

It looks like your quotes are out of whack. Since you don't appear to be
embedding any literal value into the SQL statement, I think all you need is
this:

Me!cboxVendor.RowSource = _
"Select en_vend_key, en_vend_name" & _
" From tblAdageVolumeSpend" & _
" Order By tblAdageVolumeSpend.en_vend_key;"

 

[Douglas J. Steele]

Your quotes are off.

Private Sub Form_Open(Cancel As Integer)
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name" & " From
tblAdageVolumeSpend Order By " & "tblAdageVolumeSpend.en_vend_key;"
End Sub

although I'd probably write it as

Private Sub Form_Open(Cancel As Integer)
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVolumeSpend " & _
"Order By en_vend_key"
End Sub

 

[ryan.fitzpatrick3]

Thank you, I figured it had to do with quotes. Also I get an error on
this. Error says syntax missing operator. I took this guys code who
was trying to help me but I get an error.

Private Sub cboxBranch_AfterUpdate()


If Nz(Me!cboxBranch, 0) = 0 Then
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name"
_
& " From tblAdageVendors Order By " &
"tblAdageVendors.en_vend_key;"
Else
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name
" _
& "From tblAdageVendors Where " & "tblAdageVendors.en_vend_key
= " & Me!cboxBranch & "Order By" & "tblAdageVendors.en_vend_key;"
End If


Me!cboxVendor.Requery


End Sub

 

[Douglas J. Steele]

You're missing spaces before and after "Order By" in the second case:

Private Sub cboxBranch_AfterUpdate()

If Nz(Me!cboxBranch, 0) = 0 Then
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors " & _
"Order By en_vend_key"
Else
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " &
"From tblAdageVendors " & _
"Where en_vend_key" & Me!cboxBranch & _
" Order By en_vend_key"
End If

Me!cboxVendor.Requery

End Sub

That assumes that en_vend_key is a numeric field, not text.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you, I figured it had to do with quotes. Also I get an error on
this. Error says syntax missing operator. I took this guys code who
was trying to help me but I get an error.

Private Sub cboxBranch_AfterUpdate()


If Nz(Me!cboxBranch, 0) = 0 Then
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name"
_
& " From tblAdageVendors Order By " &
"tblAdageVendors.en_vend_key;"
Else
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name
" _
& "From tblAdageVendors Where " & "tblAdageVendors.en_vend_key
= " & Me!cboxBranch & "Order By" & "tblAdageVendors.en_vend_key;"
End If


Me!cboxVendor.Requery


End Sub

 

[ryan.fitzpatrick3]

Does this code make sense? More or less I have a combobox on default
will list a complete list of vendors, I have another combobox with
locations. So if all comboboxes are blank and you select vendor
comobox it'll give you entire list, but if you select a location first
I would want the vendor cbox only to show the vendors that apply to
that location. So I have 3 codes, one in open form.

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize

Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors" & _
"Order By en_vend_key"
End Sub


One in after update of the location cbox (called cboxbranch)

Private Sub cboxBranch_AfterUpdate()


If Nz(Me!cboxBranch, 0) = 0 Then
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors " & _
"Order By en_vend_key"
Else
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors " & _
"Where en_vend_key" & Me!cboxBranch & _
" Order By en_vend_key"
End If


Me!cboxVendor.Requery

End Sub


and the last one to requery on reset.

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and
show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors" & _
"Order By en_vend_key"
'Remove the form's filter.
Me.FilterOn = False

Me!cboxVendor.Requery

End Sub



Now, when I ajusted my code with your after update one, I get a input
box that comes up when I select a plant first, but not when I select a
vendor first.

 

[Douglas J. Steele]

The concept is sound, except you can't select nothing in a combo box (i.e.:
the only way to have a Null combo box is not to select anything). Therefore,
it really doesn't make sense to check in the AfterUpdate event...

You've got to learn to pay attention to spaces! The SQL in your Form_Open
event is missing a space between the name of the table and the key words
Order By.

Another option would be to use this SQL as the Row Source of the combo box:

Select en_vend_key, en_vend_name
From tblAdageVendors
Where en_vend_key = Forms!NameOfForm!cboxBranch
Or Forms!NameOfForm!cboxBranch IS NULL
Order By en_vend_key

(that would all be a single line).

Then, in the AfterUpdate of cboxBranch, all you need is your requery:

Private Sub cboxBranch_AfterUpdate()

Me!cboxVendor.Requery

End Sub

In other words, you don't have to change the RowSource in code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Does this code make sense? More or less I have a combobox on default
will list a complete list of vendors, I have another combobox with
locations. So if all comboboxes are blank and you select vendor
comobox it'll give you entire list, but if you select a location first
I would want the vendor cbox only to show the vendors that apply to
that location. So I have 3 codes, one in open form.

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize

Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors" & _
"Order By en_vend_key"
End Sub


One in after update of the location cbox (called cboxbranch)

Private Sub cboxBranch_AfterUpdate()


If Nz(Me!cboxBranch, 0) = 0 Then
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors " & _
"Order By en_vend_key"
Else
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors " & _
"Where en_vend_key" & Me!cboxBranch & _
" Order By en_vend_key"
End If


Me!cboxVendor.Requery

End Sub


and the last one to requery on reset.

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and
show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me!cboxVendor.RowSource = "Select en_vend_key, en_vend_name " & _
"From tblAdageVendors" & _
"Order By en_vend_key"
'Remove the form's filter.
Me.FilterOn = False

Me!cboxVendor.Requery

End Sub



Now, when I ajusted my code with your after update one, I get a input
box that comes up when I select a plant first, but not when I select a
vendor first.

 

[ryan.fitzpatrick3]

Thank you. This code is not mine, it was some one elses trying to
help. The vendors are in the cboxvendors, but if I select a branch
first the same vendors are in cboxvendors and not the vendors that
only pertain to cboxbranch. That make sense?

 

[Marshall Barton]

The concept is sound, except you can't select nothing in a combo box (i.e.:
the only way to have a Null combo box is not to select anything). Therefore,
it really doesn't make sense to check in the AfterUpdate event...

However, it is possible for the row source's BoundColumn to
have a Null Value. Another way to get Null is to drag
select the existing entry and hit the Delete/Backspace key.

 

[Douglas J. Steele]

I've mentioned a couple of different possibilities. What did you choose to
implement?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you. This code is not mine, it was some one elses trying to
help. The vendors are in the cboxvendors, but if I select a branch
first the same vendors are in cboxvendors and not the vendors that
only pertain to cboxbranch. That make sense?

 

[ryan.fitzpatrick3]

I put in what you wrote. the SQL and after update.

Select en_vend_key, en_vend_name
From tblAdageVendors
Where en_vend_key = Forms!NameOfForm!cboxBranch
Or Forms!NameOfForm!cboxBranch IS NULL
Order By en_vend_key

(that would all be a single line).

Then, in the AfterUpdate of cboxBranch, all you need is your requery:

Private Sub cboxBranch_AfterUpdate()

Me!cboxVendor.Requery

End Sub


I didn't see anything else to put.

 

[ryan.fitzpatrick3]

Here's my code


Select en_vend_key, en_vend_name
From tblAdageVendors
Where en_vend_key = Forms!frmitemvolumespend!cboxBranch
Or Forms!frmitemvolumespend!cboxBranch IS NULL
Order By en_vend_key


(that would all be a single line).


Then, in the AfterUpdate of cboxBranch, all you need is your requery:


Private Sub cboxBranch_AfterUpdate()


Me!cboxVendor.Requery

 

[Douglas J. Steele]

However, it is possible for the row source's BoundColumn to
have a Null Value. Another way to get Null is to drag
select the existing entry and hit the Delete/Backspace key.

True. I sit corrected.

 

[Douglas J. Steele]

No, that doesn't make sense that if you select a value from cboxbranch that
you get all vendors in cboxvendors and not just those that pertain to
cboxbranch.

What is the bound column of cboxBranch? Once you've selected an entry in
cboxBranch (and cboxVendor doesn't change), go to the Immediate Window, type

?Forms!frmitemvolumespend!cboxBranch

(including the equal sign) and hit Enter. What value appears?

If a blank appears, try

?IsNull(Forms!frmitemvolumespend!cboxBranch)

If True appears, then cboxBranch isn't set up properly.

 

[Marshall Barton]

True. I sit corrected.

Doug, I have read this thread three times and I still can't
figure out the objective. The posted query criteria implies
that Ryan wants a way to either skip the branch or to choose
an ALL kind of branch entry (with bound column value of
Null). Seems to me that the latter is the only way to get
the AfterUpdate event to requery the vendors and have all of
them in the list. Maybe I'm just confused because aside
from that, I don't see what he wants that what he's posted
doesn't already do.

 

[ryan.fitzpatrick3]

I appoligize for not stating my questions with clarity, I am
inexperienced with VBA as you can tell, and Access for that matter. Do
you understand what I would like from reading my questions and posts
from above?

"Marshall Barton" wrote

True. I sit corrected.

Doug, I have read this thread three times and I still can't
figure out the objective.  The posted query criteria implies
that Ryan wants a way to either skip the branch or to choose
an ALL kind of branch entry (with bound column value of
Null).  Seems to me that the latter is the only way to get
the AfterUpdate event to requery the vendors and have all of
them in the list.  Maybe I'm just confused because aside
from that, I don't see what he wants that what he's posted
doesn't already do.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

 

[ryan.fitzpatrick3]

I said the opposite. If you select cboxbranch and select a brach
location, and then you go to cboxvendor the only vendors that should
show is for the branch selection you choose. Otherwise, if you want to
select a vendor first and not caring about which location(branch) then
the complete vendor list should be in cboxvendor.

 

[ryan.fitzpatrick3]

?IsNull(Forms!frmitemvolumespend!cboxBranch) = false

this is what this says.

 

[Marshall Barton]

No.

I think Doug and I are both struggling to understand what
you want to happen in what user scenarios.

Doug has provided nearly the same responses I would have and
I do not want to take over this thread. Instead of making
everyone guess what situations you want something to happen,
you should provide Doug with detailed explanations of the
several different sequwnces of user actions and how you want
the combo boxes to behave in each scenario.
--
Marsh
MVP [MS Access]

I appoligize for not stating my questions with clarity, I am
inexperienced with VBA as you can tell, and Access for that matter. Do
you understand what I would like from reading my questions and posts
from above?

Douglas J. Steele wrote:

The concept is sound, except you can't select nothing in a combo box
(i.e.:
the only way to have a Null combo box is not to select anything).
Therefore,
it really doesn't make sense to check in the AfterUpdate event...

However, it is possible for the row source's BoundColumn to
have a Null Value.  Another way to get Null is to drag
select the existing entry and hit the Delete/Backspace key.

True. I sit corrected.

Doug, I have read this thread three times and I still can't
figure out the objective.  The posted query criteria implies
that Ryan wants a way to either skip the branch or to choose
an ALL kind of branch entry (with bound column value of
Null).  Seems to me that the latter is the only way to get
the AfterUpdate event to requery the vendors and have all of
them in the list.  Maybe I'm just confused because aside
from that, I don't see what he wants that what he's posted
doesn't already do.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

 

[ryan.fitzpatrick3]

Fair enough, I'm sorry. I have a user form it has total 8 comboboxes.
All of which have either a value list or a table of selections
attached to it. The form is 100% modeled after Allen Brown's Search
Sample database

http://www.allenbrowne.com/ser-62.html

You can select a selection in one combobox or two or all and when you
click the filter button, it filters out all the information and gives
you just what you selected above. I copied all of his code and put my
information in it to make the filter work. And it does. Since all the
cbox's work properly I want to take some of the combo boxes to another
level.

Currently with no modifications from this post I can select cboxBranch
and select a branch # 8103 and run filter and all records with that
branch 8103 will pop up below. If I reset the filter and start over I
can select cboxVendor only; if I use vendor # 123456 then all branches
with #123456 attached to it will come up. This works fine.

cboxbranch has a table that lists just numerical branch #'s and branch
names. i.e. 8103, Denver Bread; this is rowsource
cboxvendor has a table that lists just numerical vendor # and vendor
names i.e 123456, Tina's Bakery; this is rowsource

Like in Allen brown's example you can click one cbox to all cbox's and
run the filter, some could be null and the filter would still run.

My question is IF I choose cbox branch first i.e. 8103 and run filter
all records for 8103 come up. OK now if I go to cboxvendor now with
8103 records still filtered, I would like the vendors in cboxvendors
not to show the entire list but to know that 8103 in cboxbranch is
choosen and only give me the vendors that 8103 uses to choose from. If
I reset the filter to stratch, then I would like the cboxvendor to
list all vendors for all branches as default, because sometimes I want
to see all brances the vendors supply us. This help at all?

So, in summary, the cboxvendors I want to default to master vendor
list, if a branch is selected first before a person goes to choose
vendor then in cboxvendor a partial vendor list will come up only for
that branch selected.

Ryan

No.

I think Doug and I are both struggling to understand what
you want to happen in what user scenarios.

Doug has provided nearly the same responses I would have and
I do not want to take over this thread.  Instead of making
everyone guess what situations you want something to happen,
you should provide Doug with detailed explanations of the
several different sequwnces of user actions and how you want
the combo boxes to behave in each scenario.
--
Marsh
MVP [MS Access]

I appoligize for not stating my questions with clarity, I am
inexperienced with VBA as you can tell, and Access for that matter. Do
you understand what I would like from reading my questions and posts
from above?

- Show quoted text -