Query for use in combobox

[benjaminkmartin]

I used the following instructions from a web page online.


To filter records in a combo/listbox based on the value selected
in another combo/listbox, you can use a stored query which uses the
first control's value as a parameter. For example,

Select PeopleID, PeopleName from tblPeople Where PeopleID = Forms!
FormName!NameOfFirstControl;

Then all you need to do is issue a Requery on the second combo/listbox
in this first control's AfterUpdate event.

Private Sub NameOfFirstControl_AfterUpdate()
Me!NameOfSecondControl.Requery
End Sub


However, when I try to enter the parameter (Location Name and Location
Code from Location ID Where Vehicle ID = Forms!Location ID and
Component ID Generator!Vehicle ID) in the query, it gives me the error
message, "Invalid bracketing of name"... what am I doing wrong?

I'm really stuck here, so any help would be GREATLY appreciated.
Thanks

 

[Lord Kelvan]

yea you shouldnt put spaces in your table or field names

pur brackets around your field names

[Location Name] and [Location Code] from [Location ID] Where [Vehicle
ID] = Forms![Location ID] and
[Component ID Generator]![Vehicle ID]

though to me that entire line looks bad are you saying one of your
tables is named location ID none of it makes sence

first you cannto use the word and in the select portion and you need
to use the word select

....
....
....

ok screw that heres what i want you to do paste me your structure for
your tables and the names of your forms and the combo box names and
ill try to help but you should really post it in
microsoft.public.access.forms

 

[benjaminkmartin]

yea you shouldnt put spaces in your table or field names

pur brackets around your field names

[Location Name] and [Location Code] from [Location ID] Where [Vehicle
ID] = Forms![Location ID] and
[Component ID Generator]![Vehicle ID]

though to me that entire line looks bad are you saying one of your
tables is named location ID none of it makes sence

first you cannto use the word and in the select portion and you need
to use the word select

...
...
...

ok screw that heres what i want you to do paste me your structure for
your tables and the names of your forms and the combo box names and
ill try to help but you should really post it in
microsoft.public.access.forms

I did post it in microsoft.public,access.forms, and no one responded
to my question.

Ok, I have a form "Location ID and Component ID Generator" with a
combobox "combo28" with two choices with 2 columns, choice 1 (column 1
= "SPH" column 2 = "1") and choice 2 (column 1 = "FAASV" column 2
="2"). The choice in this box spawns a 1 or a 2 in the text box below
"combo28". Below this text box is another combobox, "combo36".
Combo36 has several different locations, and the locations are
different for "SPH" and FAASV".

I have the choices for "SPH" and "FAASV" set up in a table, "Location
ID". I set up a query of that table. The columns of the table and
query are as follows...
Vehicle ID (which is the 1 or 2 spawned in the textbox, which is also
the second column of combo28), Location Name, Location Code (.01, .
02, .03 corresponding to the location), and then Location ID (this
serves as the key for the table, and basically is Vehicle ID +
Location Code, so you get a number like 1.02)

The way I picture the query working is when the Vehicle Name is chosen
out of combo28, the "after update" event runs a through the query and
pulls out all records where Vehicle ID = whatever the second column of
combo 28 was. And then these results are placed in combo36 with two
colomns of information, (Location Name, and Location Code).

I hope that makes sense, it's kind of difficult to explain... but
thanks for the help!

 

[Lord Kelvan]

ok first let me calarify you have two tables

location
location id
location name
location code

vehicle
vehicle id
location id
vehicle name

if this is the structure then
double click on combo28 to get the properties then set the properties
to as following

row source type: table/query
row source: SELECT vehicle.[vehicle id], vehicle.[location id],
vehicle.[vehicle name] FROM vehicle;
column count: 3
column widths: 0cm;0cm;3cm
list width: 3cm

double click on combo36 to get the properties then set the properties
to as following
row source type: table/query
column count: 3
column widths: 0cm;3cm;3cm
list width: 6cm

then set the event on combo28
afterupdate: [Event Procedure] and then click the three little dots at
the end of the line and some program code will display

you will see

Private Sub Combo28_AfterUpdate()

End Sub

change that to

Private Sub Combo28_AfterUpdate()
Combo36.RowSource = "SELECT location.[location id], location.
[location code], location.[location name] FROM location where location.
[location id] = " & Combo28.Column(1) & ";"
Combo36.Requery
End Sub

if the top table structure is correct is should just be a matter of
copy and paste

hope this helps

regards
kelvan

ps
please dont send me emails

 

[benjaminkmartin]

ok first let me calarify you have two tables

location
location id
location name
location code

vehicle
vehicle id
location id
vehicle name

if this is the structure then
double click on combo28 to get the properties then set the properties
to as following

row source type: table/query
row source: SELECT vehicle.[vehicle id], vehicle.[location id],
vehicle.[vehicle name] FROM vehicle;
column count: 3
column widths: 0cm;0cm;3cm
list width: 3cm

double click on combo36 to get the properties then set the properties
to as following
row source type: table/query
column count: 3
column widths: 0cm;3cm;3cm
list width: 6cm

then set the event on combo28
afterupdate: [Event Procedure] and then click the three little dots at
the end of the line and some program code will display

you will see

Private Sub Combo28_AfterUpdate()

End Sub

change that to

Private Sub Combo28_AfterUpdate()
    Combo36.RowSource = "SELECT location.[location id], location.
[location code], location.[location name] FROM location where location.
[location id] = " & Combo28.Column(1) & ";"
    Combo36.Requery
End Sub

if the top table structure is correct is should just be a matter of
copy and paste

hope this helps

regards
kelvan

ps
please dont send me emails

My tables are set up a little differently than that, but I think I
made all the necessary corrections.

The two tables look like this...
~Vehicle ID~
Vehicle Name
Vehicle ID

~Location ID~
Vehicle ID
Location Name
Location Code
Location ID

To explain further, "Location" refers to a location ON the vehicle,
not a place where the vehicle is located.

Except I'm getting an error that I don't know how to deal with.

I think the error has something to do with the coding in my
AfterUpdate event of combo28 which is as follows...

Private Sub Combo28_AfterUpdate()
Combo36.RowSource = "SELECT [Location ID].[Location Name], [Location
ID].[Location Code] FROM [Location ID] where [Location ID].[Vehicle
ID] = " & Combo28.Column(1) & ";"
Combo36.Requery
End Sub

When I click on combo36 I get the error message, "Data type mismatch
in criteria expression." I thought maybe the problem was that the
Vehicle ID of one table was numeric while the other was text. Buit
they are both set as text as I would like them to be. There's even an
established relationship between the Vehicle ID's of each table.

Again, Thanks for the help!

 

[Lord Kelvan]

ok you shoudlnt name tables like that it coudl confuse the databse

~Vehicle ID~
Vehicle Name
Vehicle ID


~Location ID~
Vehicle ID
Location Name
Location Code
Location ID


double click on combo28 to get the properties then set the properties
to as following

row source type: table/query
row source: SELECT [vehicle id].[vehicle id],[vehicle id].||
[vehicle name] FROM [vehicle id];
column count: 3
column widths: 0cm;0cm;3cm
list width: 3cm
double click on combo36 to get the properties then set the properties
to as following
row source type: table/query
column count: 3
column widths: 0cm;3cm;3cm
list width: 6cm

then set the event on combo28
afterupdate: [Event Procedure] and then click the three little dots at
the end of the line and some program code will display

you will see
Private Sub Combo28_AfterUpdate()

End Sub

change that to

Private Sub Combo28_AfterUpdate()
    Combo36.RowSource = "SELECT [location id].[location id], ||
[location id].[location code], [location id].[location name] FROM ||
[location id] where [location id].[vehicle id] = " & Combo28.Column(1)
& ";"
    Combo36.Requery
End Sub

if the top table structure is correct is should just be a matter of
copy and paste

do remember google groups wraps text so ill put the character || at
the end of any line that gets wrapped just remember to delete the
character and put the next line back up to the first line

hope this helps

regards
kelvan