combo box population

G

Guest

Hello,

I just can't seem to get the hang of this Access. I know how to do this in
MySQL and PHP, but Access is just different. I need to take a class, I have
a book, but it is of no help.

Here is what I need to do:

Combo box 1 has two choices:
1) Batch Number
2) Invoice Number
When one of these is selected Combo Box 2 should populate with either a list
of batch numbers and one choice of All Bathes or a list of invoice numbers
and one choice of All Invoices
Then, when one of these is selected then a button, which should be disabled
would become enabled and it will print the selected record(s).

I assume that it would be done like this:

Box 1 has an after_update in VBA code similar to
psuedo code:
if combo1="batch" then
run batch query and add option all batches
else
run invoice query and add option all invoices
end if
end sub

Combo 2 would have a after_update in VBA similar to:
psuedo code:
if combo2="all batches" then
search value="all"
else if combo2="all invoices" then
search value="all"
else
search value=me!combo2
end if
enable print button
end sub

code for print button:
psuedo code:
if combo1="batch" then
if combo2="all" then
run print batch all query
else
run print batch query
end if
else if combo1="invoice" then
if combo2="all" then
run print invoice all query
else
run print invoice query
end if
end if
end sub



Ok, so I think I have the basic strategy down, but I don't understand
creating queries with access. I also don't know how to make the button
enabled.

Thanks for any help,
Johnie Karr
Data Management Technologies
www.datamt.org
 
D

Duane Hookom

You would use the After Update event of Combo Box 1 to set the Row Source of
Combo Box 2. The Row Source could be a saved query or more commonly a SQL
statement.
Dim strSQL as String
Select Case Me.cboOne
Case "Batch"
strSQL = "SELECT... FROM tblBatchNos"
Case "Invoice"
strSQL = "SELECT ... FROM tblInvoices"
End Select
Me.cboTwo.RowSource = strSQL

The after update of combo box 2 would be code like:
Me.cmdPrint.Enabled = True

I'm not sure why you would run any queries. Just use the values from the
combo boxes to determine which report to open and which records to report.
 
J

John Vinson

Hello,

I just can't seem to get the hang of this Access. I know how to do this in
MySQL and PHP, but Access is just different.

Yep. It certainly is. said:
Here is what I need to do:

Combo box 1 has two choices:
1) Batch Number
2) Invoice Number
When one of these is selected Combo Box 2 should populate with either a list
of batch numbers and one choice of All Bathes or a list of invoice numbers
and one choice of All Invoices
Then, when one of these is selected then a button, which should be disabled
would become enabled and it will print the selected record(s).

I assume that it would be done like this:

Box 1 has an after_update in VBA code similar to
psuedo code:
if combo1="batch" then
run batch query and add option all batches
else
run invoice query and add option all invoices
end if
end sub

Actually... no. You don't need to "run" the queries.

Instead, in Combo1's AfterUpdate you would set the RowSource property
of Combo2 to the Batch query or the Invoices query as appropriate. The
RowSource is the name of a query which contains the information which
the combo is to display.
Combo 2 would have a after_update in VBA similar to:
psuedo code:
if combo2="all batches" then
search value="all"
else if combo2="all invoices" then
search value="all"
else
search value=me!combo2
end if
enable print button
end sub

Close. The Batches query could contain a special record with "All
Batches" as the visible label. The Report that you want to print could
be based on a Query, with a criterion on the Batch field

=[Forms]![YourForm]![Combo2] OR [Forms]![YourForm]![Combo2] = "All
Batches"

Your button would simply open the Report appropriate to the value of
Combo1.

John W. Vinson[MVP]
 
G

Guest

Duane,

Thanks for the reply.

Just curious....is Dim strSQL as String ....is that creating a variable
strSQL?

Also, the query would determine the population of combo box 2....either list
the batch numbers or list the invoice numbers. I'm also assuming I can use
the WHERE statement the same in that SQL code as if I'm writing it in php?
ex:
strSQL = "SELECT BatchNumber FROM tblInvoice" and nevermind because as I
type this I realize I don't need the where statement.

What would my code be for the print button to run my invoice form, but only
for the selected file in combo box 2?

Thanks,
Johnie Karr
 
G

Guest

John,

Thanks for the help. I understand the part about setting the RowSource
property of Combo2 to either the batch or invoice query....I don't know how
to do that though.

Quote:
Close. The Batches query could contain a special record with "All
Batches" as the visible label. The Report that you want to print could
be based on a Query, with a criterion on the Batch field

=[Forms]![YourForm]![Combo2] OR [Forms]![YourForm]![Combo2] = "All
Batches"

I think I understand that.

I do need to have a query though, In php/mysql it would look like this,
maybe someone can translate, and tell me how to tie that to open a report:

mysql_query("SELECT RecordID FROM Invoice1 WHERE $Combo1=$Combo2 LIMIT 1");

So, I need to make my report either only display information from RecordID
(1 record) or just no query ran and the report is just opened which should
pull all records.

Any thoughts on this?

Thanks again,
Johnie Karr

John Vinson said:
Hello,

I just can't seem to get the hang of this Access. I know how to do this in
MySQL and PHP, but Access is just different.

Yep. It certainly is. said:
Here is what I need to do:

Combo box 1 has two choices:
1) Batch Number
2) Invoice Number
When one of these is selected Combo Box 2 should populate with either a list
of batch numbers and one choice of All Bathes or a list of invoice numbers
and one choice of All Invoices
Then, when one of these is selected then a button, which should be disabled
would become enabled and it will print the selected record(s).

I assume that it would be done like this:

Box 1 has an after_update in VBA code similar to
psuedo code:
if combo1="batch" then
run batch query and add option all batches
else
run invoice query and add option all invoices
end if
end sub

Actually... no. You don't need to "run" the queries.

Instead, in Combo1's AfterUpdate you would set the RowSource property
of Combo2 to the Batch query or the Invoices query as appropriate. The
RowSource is the name of a query which contains the information which
the combo is to display.
Combo 2 would have a after_update in VBA similar to:
psuedo code:
if combo2="all batches" then
search value="all"
else if combo2="all invoices" then
search value="all"
else
search value=me!combo2
end if
enable print button
end sub

Close. The Batches query could contain a special record with "All
Batches" as the visible label. The Report that you want to print could
be based on a Query, with a criterion on the Batch field

=[Forms]![YourForm]![Combo2] OR [Forms]![YourForm]![Combo2] = "All
Batches"

Your button would simply open the Report appropriate to the value of
Combo1.

John W. Vinson[MVP]
 
B

BillCo

Thanks for the help. I understand the part about setting the RowSource
property of Combo2 to either the batch or invoice query....I don't know how
to do that though.

The code Duane gave you will do this if you attach it to the "After
Update" event on combo1. Basically, you want this to fire when the user
updates combo1. So in the properties box of the Combo1 control, select
the "events" tab and beside "after Update" select "event proceedure"
from the drop box. and eclipse button will become visible beside this.
Click it, an empty code function will be created in vb where you can
pretty much paste this in:

Dim strSQL as String
Select Case Me.cboOne
Case "Batch"
strSQL = "SELECT * FROM tblBatchNos"
Case "Invoice"
strSQL = "SELECT * FROM tblInvoices"
End Select
Me.cboTwo.RowSource = strSQL

Then do the exact same, to create the After Update event proceedure for
the second combo box, pasting in the following:
Me.cmdPrint.Enabled = True
mysql_query("SELECT RecordID FROM Invoice1 WHERE $Combo1=$Combo2 LIMIT 1");

The easiest way to approach the whole reporting side is to create two
reports, one containing details sorted by batch numbers and the other
containing details sorted by invoice numbers.
In Access when you use the "DoCmd" function to call a report, it allows
you to specify a filter, or where condition on the report. So you could
insert something like this in the "On Click" event proceedure of your
button:

If me.Combo1 = "Batch" Then
docmd.openreport "rptBatch", , , "[BatchNo] = " & me.Combo2
else
docmd.openreport "rptInvoice", , , "[InvoiceNo] = " & me.Combo2
end if

Couple of qualifiers:

1. The above presumes you are using numbers and not character
combinations for batch in invoice numbers. if not use: "[BatchNo] like
'" & me.Combo2 & "'"

2. what I've laid out doesnt allow for a "Select all" option
your row source queries could be enhanced to include this - you could
for example do a union query to a literal value.
If you do include a select all, you will need to nest in more if/else
qualifiers on the code for the button, to make sure you dont pass the
value "select all" to the report as part of a where condition.

3. A more complicated way to work it is to use the user input to build
a complete query string and actually remove/ rebuild a stored database
query using this string. Then you can launch any number of reports with
record sources that are queries linked to this initial limiting query.
It's trickier to set up but can save you going insane as people want
more and more different reports and qualifying search criteria added
over time.

Good luck!
 
G

Guest

The Batch number and Invoice number are stored on the same record....can I
just have one report?

BillCo said:
Thanks for the help. I understand the part about setting the RowSource
property of Combo2 to either the batch or invoice query....I don't know how
to do that though.

The code Duane gave you will do this if you attach it to the "After
Update" event on combo1. Basically, you want this to fire when the user
updates combo1. So in the properties box of the Combo1 control, select
the "events" tab and beside "after Update" select "event proceedure"
from the drop box. and eclipse button will become visible beside this.
Click it, an empty code function will be created in vb where you can
pretty much paste this in:

Dim strSQL as String
Select Case Me.cboOne
Case "Batch"
strSQL = "SELECT * FROM tblBatchNos"
Case "Invoice"
strSQL = "SELECT * FROM tblInvoices"
End Select
Me.cboTwo.RowSource = strSQL

Then do the exact same, to create the After Update event proceedure for
the second combo box, pasting in the following:
Me.cmdPrint.Enabled = True
mysql_query("SELECT RecordID FROM Invoice1 WHERE $Combo1=$Combo2 LIMIT 1");

The easiest way to approach the whole reporting side is to create two
reports, one containing details sorted by batch numbers and the other
containing details sorted by invoice numbers.
In Access when you use the "DoCmd" function to call a report, it allows
you to specify a filter, or where condition on the report. So you could
insert something like this in the "On Click" event proceedure of your
button:

If me.Combo1 = "Batch" Then
docmd.openreport "rptBatch", , , "[BatchNo] = " & me.Combo2
else
docmd.openreport "rptInvoice", , , "[InvoiceNo] = " & me.Combo2
end if

Couple of qualifiers:

1. The above presumes you are using numbers and not character
combinations for batch in invoice numbers. if not use: "[BatchNo] like
'" & me.Combo2 & "'"

2. what I've laid out doesnt allow for a "Select all" option
your row source queries could be enhanced to include this - you could
for example do a union query to a literal value.
If you do include a select all, you will need to nest in more if/else
qualifiers on the code for the button, to make sure you dont pass the
value "select all" to the report as part of a where condition.

3. A more complicated way to work it is to use the user input to build
a complete query string and actually remove/ rebuild a stored database
query using this string. Then you can launch any number of reports with
record sources that are queries linked to this initial limiting query.
It's trickier to set up but can save you going insane as people want
more and more different reports and qualifying search criteria added
over time.

Good luck!
 
D

Duane Hookom

Dim statements create memory variables and generally set their data type.

To open a form or report filtered to specific records, check Help on the
where clause in DoCmd.OpenForm.
 
G

Guest

I was asking because Bill said to have 2 reports....If I read his post
correctly.

Thanks for the help though!
 
B

BillCo

You dont have to - there's no prob doing just one...

sorry, I didnt realise that you had them both in the same field. that
does make life easier. everything else will still hold though.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top