468,119 Members | 1,911 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,119 developers. It's quick & easy.

Help with a list box

I need some help figuring something out. I'm not sure if I heading in
the correct direction. What I'm trying to accomplish is the following.
I have a table that
has id, discrip, and 2 date fields. I am populating this information
from a sub from on a
main form. The Id and date fields are tied together in the forms. I am
trying to get the
descrip field to show up in my main form under a listbox. I have
created a sql query to try to accomplish this. I can have multiple
lines of information for each id. That's why I'm trying to show in
list box. The problem I am having is I cannot get it to look for
specific id of the current page I'm on in the form. It prompts me for
id. If I enter the id the list box displays correct info. I would like
for it to see what page I'm on in my form take the id from there and
fill the list box accordingly. When I change sheets it would
automatically update. I have pasted sql query below. I would really
appreciate any insight on how to make this work. Thanks ahead of time.
Dan
SELECT [reserve].[site], [reserve].[reservationid],
[reservations].[reservationsid]
FROM reserve
WHERE (([reserve].[reservationid])=([reservations].[reservationsid]));

Dec 7 '05 #1
15 1539
Dan, I'm not sure I completely understand the situation you have, but I
will try to help with some general listbox information.

To populate a listbox that is bound to a table, you can simply add
records to the table and then call the ListBox.Refresh() method. This
may be useful if your listbox is on the main form but you want to
populate it from a subform. If you are doing many updates of the
listbox, this can have a lot of overhead.

You can also populate the listbox programmatically. One way is such:
Here, lstBox will indicate your listbox, and frmMain will indicate your
main form.

Forms!frmMain!lstBox.Items.Add("info you want to add")

Dec 7 '05 #2
GH
In your query:

SELECT [reserve].[site], [reserve].[reservationid],
[reservations].[reservationsid]
FROM reserve
WHERE (([reserve].[reservationid])=([reservations].[reservationsid]));

Is reservations the form name or is it supposed to be a field from a
joined table. I am not sure if this is your root problem or not, but
your query would give an error right now since the reservations tables
does not exist in the FROM clause. I would expect your query to look
more like:

SELECT [reserve].[site], [reserve].[reservationid],
[reservations].[reservationsid]
FROM reserve, reservations
WHERE (([reserve].[reservationid])=([reservations].[reservationsid]));

OR

SELECT [reserve].[site], [reserve].[reservationid],
[reservations].[reservationsid]
FROM reserve INNER JOIN reservations
ON (([reserve].[reservationid])=([reservations].[reservationsid]));

Also, you really would not need to have two reservationid fields in the
result set, since they are equivalent.

Again, this may not be causing the issue with the list box, but it
might be contributing to it.

Dec 7 '05 #3
Perhaps Im not making myself clear. I want a list box to show
information from a table in a form. The table is not the table that
main form is driving. I want the list box to see the reservationid from
the form on which ever record is active, and to show only the
information where the 2 reservation id's are =. The list box will work
if I type in reservationid, but it won't get it from the active form
page like I want. Hopefully this bettrer explains problem.

Dec 7 '05 #4
so base the listbox's rowsource on a query.

Listbox rowsource:
SELECT [Last] & ", " & [First] AS Contact
FROM olkContacts
WHERE (((olkContacts.City)=[Forms]![Form2]![Combo2]))
ORDER BY [Last] & ", " & [First];

except in your case, your WHERE clause would point to the control on
the form showing the reservationID. So your rowsource would be
something like:

SELECT [Last] & ", " & [First] AS Contact
FROM olkContacts
WHERE (((SomeTable.ReservationID=Forms![MyForm]![txtReservationID]))
ORDER BY [Last] & ", " & [First];

Dec 8 '05 #5
I have already tried this below. If I am following you you are saying
same thing.
It still won't update the listbox. Is my syntx incorrect?

SELECT [site]
FROM reserve
WHERE [reserve].[reservationid]=('[Forms]![Taking
Reservations]![ReservationsID]');

Dec 8 '05 #6
deercreek <da*@deercreekcg.com> wrote:
: I have already tried this below. If I am following you you are saying
: same thing.
: It still won't update the listbox. Is my syntx incorrect?

: SELECT [site]
: FROM reserve
: WHERE [reserve].[reservationid]=('[Forms]![Taking
^
| what are those for? _________
\
: Reservations]![ReservationsID]'); \
^ \
|___________________________________\
Dec 8 '05 #7
Are you asking about the reservationsid, Its the text box name that
holds info.

Dec 8 '05 #8
deercreek <da*@deercreekcg.com> wrote:
: Are you asking about the reservationsid, Its the text box name that
: holds info.

Do the quotes that mean that your ID is defined as a string?
I thought number which wouldn't need any quotes.
--thelma
Dec 8 '05 #9
My id is set as a text field currently. I will create my own custom
info to occupie this field once I
get the form to work.

Dec 8 '05 #10
Still need a solution. Anyone?

Dec 8 '05 #11
deercreek wrote:
Still need a solution. Anyone?


Get rid of the single quotes TL was questioning.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Dec 8 '05 #12
deercreek <da*@deercreekcg.com> wrote:
: Still need a solution. Anyone?

Is it still prompting you for the ID? If it is, it seems that
one of the table or field names you've provided is wrong:I
recently spent a long time w/ something like that and finally
noticed that the capitalization that I was using for a field
name was different from what the table actually had for the name.

Otherwise try to print out the SQL statement you're actually
using. Maybe in the listbox's setfocus event, put a
debug.print of a string variable that you've set equal to the
the SQL statement: this should show you the statement with the
actual ID value that you're asking for instead of
control.value--is it what you think that you've given it?

--thelma, muddling along with you
Dec 8 '05 #13
Deercreek:

You can't just put quotes around the field like you have done. Take
out the quotes around the [Forms]![Taking
Reservations]!ReservationsID]:

SELECT [site]
FROM reserve
WHERE [reserve].[reservationid]=([Forms]![Taking
Reservations]![ReservationsID]);

Dec 8 '05 #14
Below is the code Im using for query.
SELECT [site]
FROM reserve
WHERE ([reserve].[reservationid])=([Forms]![Taking
Reservations]![ReservationsID]);

It does not prompt me anymore what it does is it brings back the 1st
record set's information. but if I go
to the next record it gives me and pop-up ERROR
Saying : microsfoft access can not find Me!Lstsites.

Now someone else suggesting putting a requery statement in the form
under the "on current" command line and onthe reservationid "after
update " command line. The line is

Me!Lstsites.Requery

Thats when the listbox actually started showing at least the first
record set untill then it was blank.

Thats were I'm at now.

Dec 9 '05 #15
Thanks everyone I got it. requery need to go into code not just typed
in.

Thanks
Dan

Dec 9 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by wukexin | last post: by
5 posts views Thread by jhon02148 | last post: by
7 posts views Thread by Christian Christmann | last post: by
5 posts views Thread by Steve Patrick | last post: by
2 posts views Thread by Foodbank | last post: by
2 posts views Thread by duncanblacksmithmath | last post: by
1 post views Thread by Rahul | last post: by
83 posts views Thread by deppy_3 | last post: by
16 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.