By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,572 Members | 963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,572 IT Pros & Developers. It's quick & easy.

combo / form help please

P: n/a
Hello All,

I am new to Access and have been looking for answers to my problem on
the internet and have not found it yet. I hope someone can give me a
hint. Thanks so much.

I have a form (called: myform) in which there is a combo (combo1) and a
button (button1). In properties for combo1, I use "Value List" for Row
Source Type and in Row Source : "0000-0999";"1000-1999". So when the
form first load, in the combo1, there are 2 values in the drop-down
list which are: 0000-0999 and the second one is: 1000-1999. What I
want to do is that when I select the "0000-0999" value and click on the
'button1' button, a form(form1) will pops up in which there are many
columns and one column will have value from 0001 to 0999. The data are
stored in a table called: table1 (with fields like serial_no,
part_name, etc) .

I would appreciate a lot if anyone can give me a hint or show me where
to check for similar example.

Sincerely,
Teresa

Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
You don't need the button! Put the following code in the AfterUpdate event
of the combobox:
Select Case Me!Combo1
Case "0000-0999"
DoCmd.openForm "Form1"
Case "1000-1999"
<Do Something Here>
Case Else 'In Case You Add More Options To Value List
<Do Something Here>
End Select

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"motessa" <le******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Hello All,

I am new to Access and have been looking for answers to my problem on
the internet and have not found it yet. I hope someone can give me a
hint. Thanks so much.

I have a form (called: myform) in which there is a combo (combo1) and a
button (button1). In properties for combo1, I use "Value List" for Row
Source Type and in Row Source : "0000-0999";"1000-1999". So when the
form first load, in the combo1, there are 2 values in the drop-down
list which are: 0000-0999 and the second one is: 1000-1999. What I
want to do is that when I select the "0000-0999" value and click on the
'button1' button, a form(form1) will pops up in which there are many
columns and one column will have value from 0001 to 0999. The data are
stored in a table called: table1 (with fields like serial_no,
part_name, etc) .

I would appreciate a lot if anyone can give me a hint or show me where
to check for similar example.

Sincerely,
Teresa

Nov 13 '05 #2

P: n/a
On Tue, 04 Oct 2005 01:43:48 GMT, "PC Datasheet" <no****@nospam.spam>
wrote:

Select Case Me!Combo1
Case "0000-0999"
DoCmd.openForm "Form1",,,"Serial_No between '0000' and '0999'
Case "1000-1999"
DoCmd.openForm "Form1",,,"Serial_No between '1000' and '1999'
Case Else 'In Case You Add More Options To Value List
<Do Something Here>
End Select

Then Form1 would have a recordsource of:
Table1
(or better: a query based on Table1)

-Tom.

You don't need the button! Put the following code in the AfterUpdate event
of the combobox:
Select Case Me!Combo1
Case "0000-0999"
DoCmd.openForm "Form1"
Case "1000-1999"
<Do Something Here>
Case Else 'In Case You Add More Options To Value List
<Do Something Here>
End Select


Nov 13 '05 #3

P: n/a
Hi Tom,

I will give it a try today. Many thanks!

Have a great day,

Teresa

Nov 13 '05 #4

P: n/a
Hi Tom and PC,

Thank you for your great help. My form worked perfectly, just like you
said. Thanks so much.

Another question, if you have time. If I want to specify another value
for another column, let's say the field name is "Part Name" and I have
to retrieve the "part name" from a different table:

Table1:
serial_no | part_no |
0001 | 1000 |

Table 2:
part_no | part_name |
1000 | part 1 |

How do I modify the code to display value for another column (Part
Name)?

Sorry to ask too much,

Teresa

Nov 13 '05 #5

P: n/a
Why not just include part_name in Table1?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"motessa" <le******@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hi Tom and PC,

Thank you for your great help. My form worked perfectly, just like you
said. Thanks so much.

Another question, if you have time. If I want to specify another value
for another column, let's say the field name is "Part Name" and I have
to retrieve the "part name" from a different table:

Table1:
serial_no | part_no |
0001 | 1000 |

Table 2:
part_no | part_name |
1000 | part 1 |

How do I modify the code to display value for another column (Part
Name)?

Sorry to ask too much,

Teresa

Nov 13 '05 #6

P: n/a
Hi PC,

The tables are much more complicated than that and I am not the person
who controls
how many fields in a database so I don't know why. Somebody else
designs these tables.
Thanks for your help a lot. I am so new to Access!

Teresa

Nov 13 '05 #7

P: n/a
It sounds like you need some redesign of your tables! Poor design of the
tables in a database and lead to progressively more and more problems in the
database. If you need some outside help with your database I specialize in
that type of assistance. My fees are very reasonable.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"motessa" <le******@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hi PC,

The tables are much more complicated than that and I am not the person
who controls
how many fields in a database so I don't know why. Somebody else
designs these tables.
Thanks for your help a lot. I am so new to Access!

Teresa

Nov 13 '05 #8

P: n/a
Thanks PC for your suggestion. If my group needs your help, I will let
you know.

Regards,
Teresa

Nov 13 '05 #9

P: n/a
Teresa,

I would suggest that the combobox have AfterUpdate with code behind the
form that updates two invisible controls called something like MinValue
and MaxValue:

Select Case combo1
Case "0000-0999"
MinValue = "0000"
MaxValue = "0999"
Case "1000-1999"
MinValue = "1000"
MaxValue = "1999"
End Select

And then for the criteria for the field in question in the query:
Between Forms!MyForm!MinValue And Forms!MyForm!MaxValue

And the rest of this answer is more about the query by form concept in
general but not necessarily using your particular control names.

Here's something I put together that may give you some ideas about
making time periods and other criteria dynamic.

The free downloadable sample database at www.bullschmidt.com/access
uses the query by form concept so that on the invoices dialog one can
optionally choose a rep, a customer, and perhaps a date range, click on
a button that says "Input," and then have the invoice form open up
showing all the invoices that match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput
to open the invoices form with the following code behind the OnClick
property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks
(i.e. Nulls) in fields that are going to be used with Like in any
criteria for that field. Otherwise the blanks wouldn't be shown.

For example (based on what is entered into a last name search field):

Like 'Smith' would show Smith records

Like '' would show no records (probably not what one would want)

Like '*' would show all records

And to counter that I like to have the search fields have a
DefaultValue of * and not allow the search fields to be blank.

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips

<<
I am new to Access and have been looking for answers to my problem on
the internet and have not found it yet. I hope someone can give me a
hint. Thanks so much.

I have a form (called: myform) in which there is a combo (combo1) and a

button (button1). In properties for combo1, I use "Value List" for Row
Source Type and in Row Source : "0000-0999";"1000-1999". So when the
form first load, in the combo1, there are 2 values in the drop-down
list which are: 0000-0999 and the second one is: 1000-1999. What I
want to do is that when I select the "0000-0999" value and click on the

'button1' button, a form(form1) will pops up in which there are many
columns and one column will have value from 0001 to 0999. The data are
stored in a table called: table1 (with fields like serial_no,
part_name, etc) .
I would appreciate a lot if anyone can give me a hint or show me where

to check for similar example.


Nov 13 '05 #10

P: n/a
Hi Paul,

Thanks for all your help. I will follow your suggestions. Hope you have
a wonderful day.

Much appreciated,
Teresa

Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.