Trying to open report with name linked to combo box result

[vba beginner]

Hi

I would be really grateful if you could help, I am very new to VBA.

I am trying to write a code that will enable a report to be automatically opened. The report name is linked to a combo box result in a form.

The form is called Company_Details and the combo box is Store_Name_Lookup.

I have come up with the below code so far but and having problems with the stLinkCriteria.

Many thanks for any suggestions.

Private Sub Toggle84_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocCA = "Rpt_Canterbury_T_C"
stDocSI = "Rpt_Sittingbourne_T_C"
stDocST = "Rpt_Strood_T_C"
stDocCH = "Rpt_Chatham_T_C"
stDocMA = "Rpt_Maidstone_T_C"

stLinkCriteria = "Me![Store_Name_Lookup]"


Select Case [Store_Name_Lookup]
Case "Cartridge World Canterbury"
stDocName = stDocCA
Case "Sittingbourne"
stDocName = stDocSI
Case "Strood"
stDocName = stDocST
Case "Chatham"


stDocName = stDocCH
Case "Maidstone"
stDocName = stDocMA
End Select

DoCmd.OpenReport stDocName, acViewReport, , stLinkCriteria

[JoeM]

Take a look at this link here for syntax: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Remember, everything inside quotes is treated as literal text, not variables.

[pbaldy]

This includes the syntax for other data types:

BaldyWeb wherecondition

[June7]

stLinkCriteria needs the fieldname criteria should be applied to. Also, variables should be concatenated and the combobox reference is a variable. Don't put the combobox reference within quotes, concatenate, otherwise you just have the literal string 'Me![Store_Name_Lookup]' instead of the value of the combobox. I don't bother creating VBA variables when they are used only once.

Dim stDocName As String
Select Case [Store_Name_Lookup]
   Case "Cartridge World Canterbury"
      stDocName = "Rpt_Canterbury_T_C"
   Case "Sittingbourne"
      stDocName = "Rpt_Sittingbourne_T_C"
   Case "Strood"
      stDocName = "Rpt_Strood_T_C"
   Case "Chatham"
      stDocName = "Rpt_Chatham_T_C"
   Case "Maidstone"
      stDocName = "Rpt_Maidstone_T_C"
End Select
DoCmd.OpenReport stDocName, acViewReport, , "field name here='" & Me![Store_Name_Lookup] & "'"

Note use of apostrophe delimiters in the WHERE CONDITION argument. Dates use # and numbers nothing.

Are these 5 reports identical in structure? If so, why 5 reports? Why not just 1 and use the WHERE CONDITION argument? If each report is set up for a specific store, why do you need to use the WHERE CONDITION argument?

[vba beginner]

Thanks everyone, the bit that I am confused about is the "field name here" in the where condition. This part is actually the report name that will be opened. This will mean the item chosen from the combo box matches the report name and opens that report.

Sorry if I don't make sense....

[June7]

No, that is not the report name. The report name is the first argument using stDocName.

The WHERE CONDITION is the filter criteria. Filter criteria is applied to a field of the report's RecordSource. The value in the combobox must be equivalent to value in the field. You are using the Select Case structure to convert this value (the store name) into the report name.

What about my questions on the report structure?

[vba beginner]

Sorry, I didn't scroll down enough for your last question.

Each report is individual and all have different characteristics throughout so it is easier to keep them as separate reports.

What I am looking for is to be able to print a set of terms and conditions that is store specific by clicking on one button on the form. This then opens the report according to the selected store name on the form.

[June7]

So if the reports are specific to a store (if you add store must build another report) apply filter criteria in the report design so VBA code for filter string not needed.