Print a report based on a field value

[johann59]

I'm kind of new in regards to working with Access. I've worked mostly with Visual FoxPro, but they wanted this project done in Access, so...

I'm about finished with a project done in Access 2007. The only issue I'm having is coming with the code to print a report based on a value in a field.

Have 1 table, 3 reports, 2 forms.

I need help with the code for my print button, f. ex.:

--------------------------
If table1.Field1 = "A"
strDocument = "Report1"
Else
strDocument = "Report2"
End If

DoCmd.OpenReport StrDocument
--------------------------
This will of course, according to me, print the corresponding Report based on field1 having a value of either "A" or "B".

How can I tell Access to read the value from field1? I'm having trouble coming with the correct syntax. Do I have to create a recordset or a query or can I just access the field values directly?

Thanks for the help.

Johann

 

[dhookom]

Can we assume table1 has more than one record? If so, which record are you wanting to pull the value of Field1?

I think you might be going about this wrong but can't tell. Are Report1 and Report2 very similar other than they might be based on different subsets of records?

Duane
Hook'D on Access
MS Access MVP

 

[johann59]

You're right. I'm missing information on my problem.

Yes, table1 has approx 200,000 records, but all the records have a value of either "A" or "B" in field1.

The form has a 'Search' text box, where the user enters the name of a person, and then the user can press the 'Preview' button. So far its working as in the user can preview Report1, but here lies my problem. I want the user to preview either Report1 or Report2 depending on the value of Field1. If the value is A, preview Report1, else preview Report2. Right now, they can only preview each name with Report1 only.

Both reports are different, and my boss wants the user to be able to print either report depending on the value of field1.

I'm used in Visual FoxPro to dealing directly with the data. But it's been nice to work with Access. There is no better way to learn than have to tackle a project that they need 'NOW', I'm learning on the fly, but like I said before, I'm unable to get past this point, I have not had a chance to deal that much with recordsets.

Thanks Duane,

 

[johann59]

Duane, or anybody, if I could as well bother you with a 'DUMB' question, is the Visual Basic code behind both Access (2007 & 2010) based on VB6, VB.NET or just another subset called VBA? It does not seem to be VB.NET.

Thanks,

 

[dhookom]

You still didn't answer my question regarding the value of Field1 in which record and which table. Is this the value of Field1 in table1 where the name of the person matches a single record?

If so, I would probably create a combo box to select the name of a person from table1. The combo box row source would include Field1. You could then write code something like:

[green]  'columns are numbered from 0[/green]
SELECT CASE Me.cboPersonName.Column(1)
  Case "A"
   strDocument = "Report1"
  Case "B"
   strDocument = "Report2"
END SELECT

DoCmd.OpenReport StrDocument

This code is VBA and hasn't changed much since version 1 of Access. There have been lots of new functionality and bells & whistles but the core is very much the same.

Duane
Hook'D on Access
MS Access MVP

 

[johann59]

Thanks Duane for your answers,

1 Form called Form1
1 Query caled Query1
1 ListBox called LB1
# Buttons and labels
1 Table called Table1 with 200,000 records
Table has 10 fields: Field1, Last, First, Address, City, St, etc
Field1 ONLY contains a value of either an "A" or a "B" for each of the 200,000 records.

So far, the user enters a last name, and a match (or matches in case more than 1 name matches) appears in a ListBox box, then the user either:

a) finds no match, so he exits the program, or

b) finds a match in the ListBox and selects it.

So once he's selected a record, containing a name, address, city, st, zip, and a value of "A" or "B", I want the user to be able to preview/print either Report1 or Report2 based on the value of Field1, (A or B).

I would rather search field1 on table1 directly if at all possible without creating the Combo box, but if you think that's the way to go, I will attempt to do so.

Thanks,

 

[dhookom]

What happens if you have more than one John Smith in your table of 200,000 records?

What is the primary key field(s) on Table1? (hint: every table should have one)

What is the Row Source of the list box? You can add the Field1 column in the list box so you don't need a combo box.

[green]'columns are numbered from 0[/green]
SELECT CASE Me.LB1.Column(1)
  Case "A"
   strDocument = "Report1"
  Case "B"
   strDocument = "Report2"
END SELECT

DoCmd.OpenReport StrDocument

Duane
Hook'D on Access
MS Access MVP

 

[johann59]

Duane,

I do have a primary key, and if there is more than 1 match, I have the name, address, city, st, zip and account appear in the ListBox, so the user can select the appropriate name.

I will go with your suggestion and try that out, although you still left me wondering if I could kind of query 'table1.field1' thru code, but I'll leave that for later. I'll probably try and learn how to create a recordset and see how that goes.

Thank you very much for your help, I highly appreciate it.

Johann

 

[dhookom]

There is no reason to "query" the field1 value since it is most efficient to just add it as a column in the list box. You can set its width to 0.

Duane
Hook'D on Access
MS Access MVP