Queries comparing text box value to listbox

[Guest]

I'm new to Access and SQL I am probably making this more complicated then I
need to.

I have a form (frmSite) where I have a set of text boxes that derive their
value from the record source (tblSite). What I would like to do is take
the value of txtSitecode on frmSite and have a list box(lstEquipment)
where all equipment(all equipment has a sitecode assigned) in tblEquipment
with site codes matching txtSitecode is displayed. Whats the best way to
accomplish
my task. I've reviewed the boards and found plenty on JOIN and D-look up but
nothing comparing a form value to a query. Don't think D-lookup will work in
this case as it's multiple tables.

Any help would be appreicaitated.

tblSite tblEquipment
===== ==========
#Sitecode #EquipmentID
Equpiment_sitecode
Equipment_name

SELECT Equipment_sitecode,Equipment_name
FROM tblEquipment
WHERE (((tblEquipment.equipment_sitecode) =(frmSite![txtSitecode].[value]));

 

[John Vinson]

I'm new to Access and SQL I am probably making this more complicated then I
need to.

I have a form (frmSite) where I have a set of text boxes that derive their
value from the record source (tblSite). What I would like to do is take
the value of txtSitecode on frmSite and have a list box(lstEquipment)
where all equipment(all equipment has a sitecode assigned) in tblEquipment
with site codes matching txtSitecode is displayed. Whats the best way to
accomplish
my task.

You're almost there! Just needed the Forms! object. Set the RowSource
property of lstEquipment to qrylstEquipment, where that query is

SELECT Equipment_sitecode,Equipment_name
FROM tblEquipment
WHERE (((tblEquipment.equipment_sitecode)
=Forms!frmSite![txtSitecode]));

You'll want to Requery the listbox in the AfterUpdate event of
txtSitecode to keep it in synch.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,

Thanks for the reply.

Unfortunately, nothing...Although I do not get anymore errors on form load,
the list box remains blank. I checked all the form and table names and all
names are correctly spelled. I set the afterupdate in the textbox to requery
the list(thanks by the way).
I did not metion that the textbox (txtsite code) is not manually entered on
this form, instead, user navigates through the sites with the record
selector or another list box that has a bookmark feature. Could this SQL
string see this txtbox as blank?
Although I don't get errors, its still not working. Column is bound
correctly. No
more input value box, but no data either

Any addional thoughts? I appreciate your time,

Thanks,
Dave.

I'm new to Access and SQL I am probably making this more complicated then I
need to.

I have a form (frmSite) where I have a set of text boxes that derive their
value from the record source (tblSite). What I would like to do is take
the value of txtSitecode on frmSite and have a list box(lstEquipment)
where all equipment(all equipment has a sitecode assigned) in tblEquipment
with site codes matching txtSitecode is displayed. Whats the best way to
accomplish
my task.

You're almost there! Just needed the Forms! object. Set the RowSource
property of lstEquipment to qrylstEquipment, where that query is

SELECT Equipment_sitecode,Equipment_name
FROM tblEquipment
WHERE (((tblEquipment.equipment_sitecode)
=Forms!frmSite![txtSitecode]));

You'll want to Requery the listbox in the AfterUpdate event of
txtSitecode to keep it in synch.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Ok, since yesterday, was able to get the listbox issue resolved, however new
problems arose.

Turns out that the join between the two tables was incorrect. Thats why the
listEquipment was pulling up nothing. Fixed that, now listequipment does
pull up records where the txsitecode and and table are equal. However the
2nd list box on the form that used findfirst and bookmark no longer pulls up
the the correct site info. Back to the drawing board

John,

Thanks for the reply.

Unfortunately, nothing...Although I do not get anymore errors on form load,
the list box remains blank. I checked all the form and table names and all
names are correctly spelled. I set the afterupdate in the textbox to requery
the list(thanks by the way).
I did not metion that the textbox (txtsite code) is not manually entered on
this form, instead, user navigates through the sites with the record
selector or another list box that has a bookmark feature. Could this SQL
string see this txtbox as blank?
Although I don't get errors, its still not working. Column is bound
correctly. No
more input value box, but no data either

Any addional thoughts? I appreciate your time,

Thanks,
Dave.

I'm new to Access and SQL I am probably making this more complicated then I
need to.

I have a form (frmSite) where I have a set of text boxes that derive their
value from the record source (tblSite). What I would like to do is take
the value of txtSitecode on frmSite and have a list box(lstEquipment)
where all equipment(all equipment has a sitecode assigned) in tblEquipment
with site codes matching txtSitecode is displayed. Whats the best way to
accomplish
my task.

You're almost there! Just needed the Forms! object. Set the RowSource
property of lstEquipment to qrylstEquipment, where that query is

SELECT Equipment_sitecode,Equipment_name
FROM tblEquipment
WHERE (((tblEquipment.equipment_sitecode)
=Forms!frmSite![txtSitecode]));

You'll want to Requery the listbox in the AfterUpdate event of
txtSitecode to keep it in synch.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

John,

Thanks for the reply.

Unfortunately, nothing...Although I do not get anymore errors on form load,
the list box remains blank. I checked all the form and table names and all
names are correctly spelled. I set the afterupdate in the textbox to requery
the list(thanks by the way).
I did not metion that the textbox (txtsite code) is not manually entered on
this form, instead, user navigates through the sites with the record
selector or another list box that has a bookmark feature. Could this SQL
string see this txtbox as blank?
Although I don't get errors, its still not working. Column is bound
correctly. No
more input value box, but no data either

I'm not understanding where the txtsite code comes from. What is the
Control Source of the textbox? Does the txtsite value exist (reliably)
on some *other* form, and could you use that form reference in the
listbox's rowsource?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Ok, since yesterday, was able to get the listbox issue resolved, however new
problems arose.

Turns out that the join between the two tables was incorrect. Thats why the
listEquipment was pulling up nothing. Fixed that, now listequipment does
pull up records where the txsitecode and and table are equal. However the
2nd list box on the form that used findfirst and bookmark no longer pulls up
the the correct site info. Back to the drawing board

Without seeing your structure or you code, all I can suggest is that a
Requery instantly invalidates all bookmarks on the requeried
recordset. Bookmarks are only of value if the recordset is pretty much
static.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John... I am good now. Thanks for your help! After much toying around, the
form is doing what it should including the bookmarks!

Thanks,
Dave