Why is this an error?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryan.fitzpatrick3@safeway.com - 06 Oct 2008 18:12 GMT

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

Reply to this Message

Dirk Goldgar - 06 Oct 2008 18:21 GMT

> 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;"

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Douglas J. Steele - 06 Oct 2008 18:25 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

ryan.fitzpatrick3@safeway.com - 06 Oct 2008 18:36 GMT

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

On Oct 6, 10:25 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> Your quotes are off.
>
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Douglas J. Steele - 06 Oct 2008 19:05 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Your quotes are off.
>
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

ryan.fitzpatrick3@safeway.com - 06 Oct 2008 20:06 GMT

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.

On Oct 6, 11:05 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> You're missing spaces before and after "Order By" in the second case:
>
[quoted text clipped - 82 lines]
>
> - Show quoted text -

Reply to this Message

Douglas J. Steele - 06 Oct 2008 20:32 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> You're missing spaces before and after "Order By" in the second case:
>
[quoted text clipped - 82 lines]
>
> - Show quoted text -

Reply to this Message

ryan.fitzpatrick3@safeway.com - 06 Oct 2008 21:53 GMT

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?

On Oct 6, 12:32 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> 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,
[quoted text clipped - 186 lines]
>
> - Show quoted text -

Reply to this Message

Douglas J. Steele - 06 Oct 2008 22:11 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> The concept is sound, except you can't select nothing in a combo box
> (i.e.:
[quoted text clipped - 191 lines]
>
> - Show quoted text -

Reply to this Message

ryan.fitzpatrick3@safeway.com - 06 Oct 2008 22:22 GMT

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.

On Oct 6, 2:11 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> I've mentioned a couple of different possibilities. What did you choose to
> implement?
[quoted text clipped - 211 lines]
>
> - Show quoted text -

Reply to this Message

ryan.fitzpatrick3@safeway.com - 06 Oct 2008 22:44 GMT

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

Reply to this Message

Douglas J. Steele - 07 Oct 2008 01:31 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Here's my code
>
[quoted text clipped - 11 lines]
>
> Me!cboxVendor.Requery

Reply to this Message

ryan.fitzpatrick3@safeway.com - 07 Oct 2008 18:25 GMT

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.

On Oct 6, 5:31 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> 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
[quoted text clipped - 38 lines]
>
> - Show quoted text -

Reply to this Message

ryan.fitzpatrick3@safeway.com - 07 Oct 2008 18:27 GMT

?IsNull(Forms!frmitemvolumespend!cboxBranch) = false

this is what this says.

On Oct 6, 5:31 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

Reply to this Message

Marshall Barton - 06 Oct 2008 22:08 GMT

>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Douglas J. Steele - 07 Oct 2008 01:27 GMT

>>The concept is sound, except you can't select nothing in a combo box
>>(i.e.:
[quoted text clipped - 5 lines]
> 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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Reply to this Message

Marshall Barton - 07 Oct 2008 04:41 GMT

>> Douglas J. Steele wrote:
>>
[quoted text clipped - 9 lines]
>
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

ryan.fitzpatrick3@safeway.com - 07 Oct 2008 17:07 GMT

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:
>
[quoted text clipped - 25 lines]
>
> - Show quoted text -

Reply to this Message

Marshall Barton - 07 Oct 2008 19:05 GMT

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.

Signature

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
[quoted text clipped - 30 lines]
>>
>> - Show quoted text -

Reply to this Message

ryan.fitzpatrick3@safeway.com - 07 Oct 2008 21:43 GMT

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.
>
[quoted text clipped - 49 lines]
>
> - Show quoted text -

Reply to this Message

Marshall Barton - 09 Oct 2008 01:37 GMT

>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
[quoted text clipped - 37 lines]
>vendor then in cboxvendor a partial vendor list will come up only for
>that branch selected.

If I understand all that, Doug already provided a way to to
get whant you want. As Doug said, make sure cboxBranch's
BoundColumn is set so it corresponds with the en_vend_key
field.

Signature

Marsh
MVP [MS Access]

Reply to this Message

ryan.fitzpatrick3@safeway.com - 09 Oct 2008 22:39 GMT

Doug,

I got it to work how I wanted to, what I did is use this.

Private Sub cboxBranch_AfterUpdate()
Dim strSQL As String
If Nz(Me!cboxBranch, 0) = 0 Then
' If the combo is Null, use the whole table as the RecordSource.
Me!cboxVendor.RowSource = "tblAdageVendors"
Else

Me!cboxVendor.RowSource = "qryvendorbranch"
End If
End Sub

I saved the sql statement as a query, and i'm just using the query. in
place of a sql string.

Also i have a reset filter button so I put this in Me!
cboxVendor.RowSource = "tblAdageVendors"

and it works. I have one question though. how do I change bound column
and column's and column's widths, because between the two seperate
rowsources, one has column bound 1, width 1, column 1, and the other
has column bound 2, width 0;1 and column 2. how can i program this
into the vba code?

ryan

On Oct 6, 12:32 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

Reply to this Message