How to pass a value from a form to a query

  • Thread starter Thread starter Jerry Crosby
  • Start date Start date
J

Jerry Crosby

I have an unbound combo box on a form. Form name: frmTest; combo box name:
cboStartDate.

I have a command button on that same form that runs a query.

I need to use the value in the cboStartDate field in that query.

How do I reference that combo box value in the query code? I've tried using
"frmTest.cboStartDate" and "Me.cboStartDate" but it still prompts me for the
value, which tells me the code isn't grabbing the value from the form.

I know I could just use a simple query prompt and have the user enter the
StartDate in that box, but I'm using a pop-up calendar to help the user
select what date to enter in the cboStartDate field (and the query prompt is
so elementary!).

Hope I've explained it well enough. If not, ask questions.

Thanks in advance.

Jerry
 
Jerry

The first thing that comes to mind is that since your combobox is unbound,
perhaps on the afterupdate event of the combobox, you could populate a
hidden textbox on your form with the value you want to use in your query and
then reference the textbox in your query.

HTH

Debra
 
Jerry

What value/data and datatype is in the first column of the cboStartDate
combobox and what is the datatype of the field you are attempting to insert
this value as criteria?

Are you getting any results and just the wrong results or nothing at all?

Perhaps you can post the query SQL here as well as the rowsource of your
combobox.

Debra
 
Hey one answer I might know after asking everyone else questions!

Use this in your criteria...

Forms!yourform!yourtextbox

Hope this works.

Mike
 
Thanks for sticking with me, Debra!

First of all, the combo box issue is somewhat out of the picture now,
because I used your idea and had the combo box populate a textbox and I'm
trying to use the value of that textbox in the query.

To back up further, I'm trying to combine two ideas I found online. The
query design is from the Microsoft Knowledge Base article 245074 (How to
Query a Reservation Database by Date for Room Availability). It gave me the
SQL code for the two queries involved. I have gotten that to work.

Rather than have the user respond to a query parameter prompt window for the
two dates, I want to use a pop-up calendar. I got that at
http://www.fontstuff.com/access/acctut09.htm. It makes a slick pop-up
calendar from which the user can "pick" the two dates. I've gotten that
element to work, too.

But when combining the two, it gags. No matter what I've done, I continue
to get the query prompt windows. I'm trying to pass the values selected in
the pop-up calendar routine to the query (which shows a table of the rooms
available within the two dates given).

I'd be glad to send you the query code, but since it relies on another query
I'd think it would just muddy the waters more.

If we can't get it working shortly, I'll take a break and sleep on it.

Jerry
 
Hi Jerry

I'm just heading off to work for a few hours but will be happy to look at it
when I get back

I also use pop-up calendars ALL the time as I do not trust users to input
dates in the correct format so that subsequent queries return appropriate
results (naturally I had to learn that the hard way).

It would still be helpful though if we knew what datatype the data is in
your underlying field.

If you want, send me the database and I'll peek at it when I get home and
post a response back here. My email is as it appears here minus the NOSPAM
part.

(e-mail address removed)

We WILL get this working this evening (by the way I'm in Ontario EST and it
is currently 4:53 p.m.here) .....

Debra
 
What's wrong with my code. I am trying to use a pop up calendar to
pick date. I already have a date in the fields Start_Date and
End_Date. But when the calendar first pops up it does not pull those
values. Plus the first time I click on it it does not put the correct
date back in the field. Once I use it and get the bad data in then it
will work fine??

Here is my code.

Thanks,
Eric

Private Sub Start_Date_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
asgcal.Visible = True
asgcal.SetFocus
asgcal.Value = Start_Date.Value

End Sub
Private Sub asgcal_Click()
Start_Date.Value = asgcal.Value
Start_Date.SetFocus
asgcal.Visible = False

End Sub
Private Sub End_Date_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
asgcal2.Visible = True
asgcal2.SetFocus
asgcal2.Value = End_Date.Value

End Sub
Private Sub asgcal2_Click()
End_Date.Value = asgcal2.Value
End_Date.SetFocus
asgcal2.Visible = False

End Sub
 
Back
Top