ACCESS FEED: RE: [MS_AccessPros] Re: Using three comboboxes to access/update data in a table?

Selasa, 20 November 2012

RE: [MS_AccessPros] Re: Using three comboboxes to access/update data in a table?

Tony

Easy enough. There should be only one WHERE clause. To use more than one criterion, you use and AND like this:

comboArticle.RowSource = "Select DISTINCT tableWarehouseData.Article " & _
"FROM tableWarehouseData " & _
"WHERE tableWarehouseData.Loc = '" & comboLocation.Value & "' " & _
"AND tableWarehouseData.Ticket = " & comboTicket.Value & " " & _
"ORDER BY tableWarehouseData.Article;"

Regards,

Bill

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Gkillah36
Sent: Monday, November 19, 2012 6:21 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Using three comboboxes to access/update data in a table?

Ah, that works, thank you Bill! However, there's one thing I failed to account for:

I need the source of comboArticle to be based on both the value of comboTicket AND comboLocation. So I tried to change my comboLocation_AfterUpdate procedure to:

Private Sub comboLocation_AfterUpdate()

comboArticle.RowSource = "Select DISTINCT tableWarehouseData.Article " & _
"FROM tableWarehouseData " & _
"WHERE tableWarehouseData.Loc = '" & comboLocation.Value & "' " & _
"WHERE tableWarehouseData.Ticket = " & comboTicket.Value & " " & _
"ORDER BY tableWarehouseData.Article;"
End Sub

However, I get an error as soon as I click on the comboArticle combobox that says:

"Syntax error (missing operator) in query expression 'tableWarehouseData.Loc = 'z' WHERE tableWarehouseData.Ticket = 8'."

What's wrong? I'm guessing that it's probably something really simple, haha. Can you please take a look at form formWarehouseData again? I've uploaded my zipped database to "2_AssistanceNeeded" again. It is now named " PracticeNASDatabase_111912a.zip."

Thanks!

Tony

-----Original Message-----
From: Bill Mosca <wrmosca@comcast.net <mailto:wrmosca%40comcast.net> >
To: MS_Access_Professionals <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> >
Sent: Mon, Nov 19, 2012 12:21 pm
Subject: [MS_AccessPros] Re: Using three comboboxes to access/update data in a table?

Tony

I found the error. It was my fault. The line should have been:
Me.FilterOn = True

I also found another small error. Only text data types need single quotes around them. Numbers need nothing. Dates need hashes (#).

Here is what the procedure should look like:
Private Sub comboArticle_AfterUpdate()

Me.Filter = "tableWarehouseData.Ticket = " & Me.comboTicket _
& " AND tableWarehouseData.Loc='" & Me.comboLocation _
& "' AND tableWarehouseData.Article = '" & Me.comboArticle & "'"
Me.FilterOn = True
End Sub

Bill

--- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , Gkillah36 <GKillah36@...> wrote:
>
>
> Thank you very much Bill!
>
> I tried what you suggested, but I'm getting an error: "Compile error: Invalid use of property." It looks like it's complaining about the "Me.FilterOn" part.
>
> Can you please take a look at the form formWarehouseData? I've uploaded my zipped database to the "2_AssistanceNeeded" folder in the "Files" section as you suggested. My file is "PracticeNASDatabase_111912.zip."
>
> Much appreciated!
>
> Tony
>
>
>
>
> -----Original Message-----
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> >
> Sent: Mon, Nov 19, 2012 10:11 am
> Subject: [MS_AccessPros] Re: Using three comboboxes to access/update data in a table?
>
>
>
>
>
> Hi Tony
>
> Welcome to our group. Firstly, you don't have to email your database to any one responder. The way we do things here is to compact the db, zip it and upload it to our Files>Assistance Needed folder.
>
> About getting the right record to display... using the third combo's AfterUpdate event, you filter the form's records based on teh three criteria. Something like this:
>
> Private Sub cboZIPCode_AfterUpdate()
> Me.Filter = "LastName = '" & Me.cboLastName _
> & "' AND CustState='" & Me.cboCustState _
> & "' AND ZIPCode = '" & Me.cboZIPCode & "'"
> Me.FilterOn
>
> End Sub
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
>
> --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , "Tony" <GKillah36@> wrote:
> >
> > Hi all,
> >
> > I have three comboboxes in a form. When the user selects a value from the first one, the source for the second combobox changes, then when the user makes the selection from the second combobox, the source for the third combobox changes as well.
> >
> > My question is, how do I make it so that when a user makes their selection from all three comboboxes, the correct data from the same table those values come from appears in a fourth textbox there?
> >
> > Any help is much appreciated. I can e-mail you my database if you respond with your e-mail address
> >
> > Thank you very much!
> >
> > Tony
> >
> > P.S. - I use MS Access 2010.
> >
>
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]