Combobox Rowsource and Inputbox

[Mark R.]

What I want: when I click on my combobox, it prompts me
to input a date upon which to to select records from the
table to list in the combo box

What I got: the combobox only prompts me the first
time I click it, not subsequent times.

Why I think this happened: I used Parameter in my query
and not input box

What I've tried: assigning an input box output to
a "dim variable name as date" in Gotfocus or Click, but to
no avail

Current Code: in the rowsource of the combox is this
query:


Parameters(beginningDate]DateTime;
Select table.ID, table.yada
from table
where(
(table.xdate>=beginningdate And
table.xdate<=(beginningdate+1)
))
Order by table.ID

In code I have:

Private sub comboselectdate_Afterupdate()
Dim rs as DAO.recordset
Set rs = Me.RecordsetClone
With rs
..Findfirst "ID = " & Me.Comboselectdate
..Edit 'Mode
!Combodate = Date
..Update
Me.Bookmark = .Bookmark
End With
Me.Combo39.Requery
Me.Combo43.Requery
End Sub

This all does prompt me for a date, select table records
for the date, list the date in the combobox, and if I
select one of those records in the list, that record
appears in Combobox39 and Combobox43 as I want.

But again, if I go to select a different date, it does not
prompt me for a date again, it just uses the first date
originally input.

 

[John Vinson]

What I want: when I click on my combobox, it prompts me
to input a date upon which to to select records from the
table to list in the combo box

Would you settle for a slightly different, perhaps more convenient
approach? If you have an unbound Textbox on the form containing the
date field, you can use *IT* as the criterion, and requery the combo
in the textbox's AfterUpdate event.

Parameters(beginningDate]DateTime;
Select table.ID, table.yada
from table
where(
(table.xdate>=beginningdate And
table.xdate<=(beginningdate+1)
))
Order by table.ID

Just change this to

Paramerers [Forms]![MyFormName]![txtDate] DateTime;

and in the textbox's AfterUpdate event simply have one line:

Me!comboselectdate.Requery

Do the same for the other combo boxes.

 

[mark r]

I will try it......... what is the reasoning, what do
these ! and [ ] refer to and do?

-----Original Message-----

What I want: when I click on my combobox, it prompts me
to input a date upon which to to select records from the
table to list in the combo box

Would you settle for a slightly different, perhaps more convenient
approach? If you have an unbound Textbox on the form containing the
date field, you can use *IT* as the criterion, and requery the combo
in the textbox's AfterUpdate event.

Parameters(beginningDate]DateTime;
Select table.ID, table.yada
from table
where(
(table.xdate>=beginningdate And
table.xdate<=(beginningdate+1)
))
Order by table.ID

Just change this to

Paramerers [Forms]![MyFormName]![txtDate] DateTime;

and in the textbox's AfterUpdate event simply have one line:

Me!comboselectdate.Requery

Do the same for the other combo boxes.


.

 

[John Vinson]

I will try it......... what is the reasoning, what do
these ! and [ ] refer to and do?

They are delimiters. Square brackets optionally enclose the name of a
Collection or of an object such as a Form or a Control - they are
obligatory if that name contains blanks or special characters; the !
character means "look in this collection for a member". So

[Forms]![frmCrit]![txtDate]

means "Look in the Forms collection (i.e. all open forms); select the
member named frmCrit; look in its default collection (i.e. its
controls) for a control named txtDate; and return the value in that
control.

 

[mark r.]

thanks........but I still have a problem.

I get no error messages, but the combobox still does not
prompt me for a new date on subsequent clicks.

Paramerers [Forms]![MyFormName]![txtDate] DateTime;

I tried typing exactly:

[Forms]![Logsheet]![beginningdate]DateTime

I also tried:
Forms!Logsheet!beginningdate DateTime
Forms!Logsheet![beginningdate]DateTime

I tried putting the above in my Where clause
as well as my Parameter statement.

I tried putting the Requery statement in a _Click()
procedure

I tried putting
Forms!Logsheet!beginningDate = Input("enter it")
into a command button next to my combobox

I think I need a better understanding of the underlying
logic to troubleshoot this one.

 

[mark R.]

Great explnation, except I am not sure of

member named frmCrit; look in its default collection

(i.e. its controls) for a control named txtDate; and
return the value in that control.

I thought txtdate was my beginningDate of my SQL procedure.
I call beginningDate a variable inwhich the user inputs a
prompted value. And so I guess you are saying that
txtDate is a variable and variables are considered
controls. But I thought controls are objects users click
on to launch an event. So know I guess a control is any
object, whether it contains a value or an event procedure.

So why is it necessary to add that "tree" Froms!formname!
variablename to the parameter statement?

In any case, your solution as I implemented it still does
not work. Please read my previous posting "STILL A
PROBLEM" under this thread for details on what is still
going wrong.

Thanks for your patience and assistance. I truly
appreciate it.

 

[mark r]

Actually Agent did not break the thread.....just look one
+ sign level up, it has STILL A PROBL in the subject line.

I am starting to see what you mean about reading the text
box contorl for beginning date. Still fuzzy though....
Step 1 user types beginning date into text box
Step 2 row source of combobox calls query
I guess the parameter statement of the query
finds and reads
that "step 1" textbox using the "tree" name you
taught me.
Step 3 If the user later changes the text box
teh combobox will use the new date so no problem
there.
Question 1 why does Afterupdate() need to requery
in this case?

 

[mark R]

I followed my steps and the query still prompts me for
variable input.

- I typed into the query Parameters and Where clause
Parameters Forms!Logsheet!Beg_txt_Date DateTime

I assume Beg_txt_Date is the name of the text box control
in Logsheet where the user types in the date he wants to
select records from

-In the Row Source of the combobox I typed the name of the
query

I fillin the text box
I click on the combobox
I get a prompt asking me to give input for
Forms!Logsheet!Beg_txt_Date

I thought you said the query would go look up the date
from the text box using the "tree" address and substitute
that date value into the Parms and Where clause statements.

I am still obviously not clear

 

[John Vinson]

Parameters Forms!Logsheet!Beg_txt_Date DateTime
Select Logtable.ID Logtable.lname
From Logtable
Where (Logtable.xdate >= Forms!Logsheet!Beg_txt_Date And
Logtable.xdate<=(Forms!Logsheet!Beg_txt_Date +1))
Order by Logtable.ID;

Try putting a semicolon after DateTime and before the Select, and a
comma after ID: also maybe use DateAdd to add a day to the date.

Parameters [Forms]![Logsheet]![Beg_txt_Date] DateTime;
Select Logtable.ID, Logtable.lname
From Logtable
Where (Logtable.xdate >= [Forms]![Logsheet]![Beg_txt_Date] And
Logtable.xdate <= DateAdd("d", 1, [Forms]![Logsheet]![Beg_txt_Date])
Order by Logtable.ID;