Auto-populate fields with combo box AND date range

[Jill]

Hi all,

I have a database where I would like the user to be able to select the pond
name and then either one date in the beginning date field or a range of dates
in beginning and ending date fields. I envision a pop-up box with a combo
box to select the pond name, and beginning and ending date fields for entry,
and upon selecting these, this pop-up box will disappear and a form with pond
sample data for these dates will open and be populated.

Or I can do the combo box and beginning and ending date on the form itself,
probably easier...

Guidance on how to do this would be appreciated! I have downloaded Roger
Carlson's cascading combo boxes example and have the idea for the combo box
limiting the selection for the next combo box. But in my situation, the next
"combo box" would be the dates, and obviously a combo box isn't the best
format... and the bigger question is how to get the two to work together to
pull up the data.

Thank you!

Jill

 

[Steve]

Hello Jill,

It doesn't sound like your tables are designed correctly! They should look
like:
TblPond
PondID
PondName
etc

Assuming your dates are dates you fish the pond, you need a pond fishing
table:
TblPondFishing
PondFishingID
PondID
BeginningDate
EndingDate
NumberFishCaught

Then you need a form/subform. The main form is based on TblPond and the
subform is based on
TblPondFishing. You can use the form/subform to enter/edit and delete data
from either or both tables. Also you can use the form to display your search
results. Your pop-up form with a combo box would be the search form. The
combobox would display the pond name but have the value PondID. The user
would select the pond name and code would open the form to the selected pond
and the subform would display all the fishing dates.

Give me your comments and then we will work on the code.

Steve
(e-mail address removed)

 

[Gina Whipp]

Jill,

You can use text boxes to select the range, however, do you only want them
to select specific dates OR any data between any two dates?

You can do...

Combo Box to select the pond and have a form open which has two text boxes
to enter dates and a button to run the filter. OR you can have a form to
select all three options and open a form. Instead of cascading combos, have
a look at...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Hi all,

I have a database where I would like the user to be able to select the pond
name and then either one date in the beginning date field or a range of
dates
in beginning and ending date fields. I envision a pop-up box with a combo
box to select the pond name, and beginning and ending date fields for entry,
and upon selecting these, this pop-up box will disappear and a form with
pond
sample data for these dates will open and be populated.

Or I can do the combo box and beginning and ending date on the form itself,
probably easier...

Guidance on how to do this would be appreciated! I have downloaded Roger
Carlson's cascading combo boxes example and have the idea for the combo box
limiting the selection for the next combo box. But in my situation, the
next
"combo box" would be the dates, and obviously a combo box isn't the best
format... and the bigger question is how to get the two to work together to
pull up the data.

Thank you!

Jill

 

[Jeff Boyce]

The OP didn't mention tables. I'm curious as to the basis on which you are
making your assessment that her tables aren't correctly designed?

Regards

Jeff Boyce
Microsoft Access MVP

 

[John... Visio MVP]

It is just another of steve's ploys to get the OP to contact him off list so
he can make his pitch for work.

John... Visio MVP

 

[Steve]

If the OP has a pond table with only one beginning date field and one ending
date field, there would be no need to search on dates. Since she wants to
search on dates, I presumed she has a table that looks like:
TblPond
PondID
PondName
BeginningDate1
EndingDate1
BeginningDate2
EndingDate2
BeginningDate3
EndingDate3
.......

which you know as well as I do that that structure is incorrect. Further,
she said "..... the beginning date field or a range of dates in beginning
and ending date fields." I interpret this to mean she has the above
structure.
I said "It doesn't sound like your tables are designed correctly!" Based on
the way I said that, the OP should interpret it to say I made an assumption
and if my assumption is incorrect, she can respond and tell me. Then I can
respond back to her correction.

This type of dialog is a tool of a good consultant. It elicits information
much better than just saying "more informaton please". Notice Ms. Whipp
responded without knowing anything about how the OP's tables are set up.

Regards,

Steve

 

[Jeff Boyce]

Before you offer a way to correct something, wouldn't you want to verify
that your presumption was accurate?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Steve]

In many instances a good consultant does not do it that way. If the
presumption is correct, the OP goes about his business. If the presumption
is wrong, it spurs the OP to thinking and elicits a much more informative
response back from the OP. Case in point - look at many of the poorly
written posts and the first responses to the post. Then look at the number
of posts back and forth. Finally, analyze the thread and see if the problem
was ever thoroughly solved for the OP.

Regards,

Steve
(e-mail address removed)

 

[Jill]

Oh my goodness. I had a nice laugh this morning. Because of the irony of it
all.

I agree with Jeff. "I'm curious as to the basis on which you are making your
assessment that her tables aren't correctly designed?"

Based on the example tables Steve provided (although mine doesn't have to do
with fishing), my tables are designed just like that, and data entry IS done
via forms and subforms, and editing is to be done vs. forms and subforms as
well, only I’m wanting to figure out how to have the user select pond name
and date(s) before delving into it further. I have just ONE date field in my
table – sample date. It was simply a matter of perhaps my not using the
right terminology. I used the words "beginning" and "ending" for date fields
that led to the assumption of actually having two fields. No, I have ONE
date field, on which I want to search for one date or a number of dates. The
"beginning" and "ending" fields would just be form fields/text boxes in which
the user would select the range of dates.


Steve said, "This type of dialog is a tool of a good consultant. It elicits
information
much better than just saying "more informaton please".

I like how this dialogue went... back and forth about assumptions and
whatnot and not disseminating the nature of the problem. And offending the
OP (me!) at the beginning about assuming my table structure is incorrect.


Steve said, "Notice Ms. Whipp responded without knowing anything about how
the OP's tables are set up."

The funny thing is, she pretty much got to the nature of my problem, or at
least closest to it. AND in her first response, too!


Steve said, "In many instances a good consultant does not do it that way.â€

I would not want to work with you, Steve, as a consultant if you don't get
to the crux of the situation first before making assumptions about your
client, which offends him/her. I’ve worked as a consultant myself, albeit
not in the database field. Your assumptions could be avoided, and the
general "more information please" could be avoided by simply saying, "Give me
an example of your tables' structure." If you ARE wishing to make
assumptions - erring on the safer side would be to assume I have my table
structure set and hopefully correct, especially since I'm at the point of
asking how to filter/search data fields. Then if I still have problems, I'd
be posting back saying this still doesn't work, then that would maybe hint at
a deeper problem, yes, perhaps the table structure.


Ok, back to the problem. I’m going to take a look at the website Gina
provided.

Jill

 

[Gina Whipp]

Jill,

And we'll be waiting should you have any further questions...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Oh my goodness. I had a nice laugh this morning. Because of the irony of
it
all.

I agree with Jeff. "I'm curious as to the basis on which you are making your
assessment that her tables aren't correctly designed?"

Based on the example tables Steve provided (although mine doesn't have to do
with fishing), my tables are designed just like that, and data entry IS done
via forms and subforms, and editing is to be done vs. forms and subforms as
well, only I'm wanting to figure out how to have the user select pond name
and date(s) before delving into it further. I have just ONE date field in
my
table - sample date. It was simply a matter of perhaps my not using the
right terminology. I used the words "beginning" and "ending" for date
fields
that led to the assumption of actually having two fields. No, I have ONE
date field, on which I want to search for one date or a number of dates.
The
"beginning" and "ending" fields would just be form fields/text boxes in
which
the user would select the range of dates.


Steve said, "This type of dialog is a tool of a good consultant. It elicits
information
much better than just saying "more informaton please".

I like how this dialogue went... back and forth about assumptions and
whatnot and not disseminating the nature of the problem. And offending the
OP (me!) at the beginning about assuming my table structure is incorrect.


Steve said, "Notice Ms. Whipp responded without knowing anything about how
the OP's tables are set up."

The funny thing is, she pretty much got to the nature of my problem, or at
least closest to it. AND in her first response, too!


Steve said, "In many instances a good consultant does not do it that way."

I would not want to work with you, Steve, as a consultant if you don't get
to the crux of the situation first before making assumptions about your
client, which offends him/her. I've worked as a consultant myself, albeit
not in the database field. Your assumptions could be avoided, and the
general "more information please" could be avoided by simply saying, "Give
me
an example of your tables' structure." If you ARE wishing to make
assumptions - erring on the safer side would be to assume I have my table
structure set and hopefully correct, especially since I'm at the point of
asking how to filter/search data fields. Then if I still have problems, I'd
be posting back saying this still doesn't work, then that would maybe hint
at
a deeper problem, yes, perhaps the table structure.


Ok, back to the problem. I'm going to take a look at the website Gina
provided.

Jill