VBA Help

[all7holly]

When I enter the vendor, then media type, then project expense TAT. It should give me the correct price when it has met all the criteria however it is not matching with the correct price for the entered criteria. Any thoughts?

strsql = "SELECT * from [tbl sr lab rates] where [lab] ='" & Me.Vendor & "'"
strsql = "SELECT * from [tbl sr lab rates] where [sample description] ='" & Me.MediaType & "'"
strsql = "SELECT * from [tbl sr lab rates] where [labratesTAT] ='" & Me.projectexpenseTAT & "'"

 

[pbaldy]

Is that your actual code? If so, the only line being used is the last one. You'd want something like this to test all 3 fields:

strsql = "SELECT * from [tbl sr lab rates] where [lab] ='" & Me.Vendor & "' And [sample description] ='" & Me.MediaType & "' And [labratesTAT] ='" & Me.projectexpenseTAT & "'"

Which assumes all 3 fields are text.

 

[John Big Booty]

Welcome to the forum.

I'm not entirely sure what you are trying to achieve with the code you present. I might help if you showed us the code in it's entirety.

However from what you have presented, if you are using the variable strsql in DoCmd.RunSQL, then only the final SQL string will be passed to the command.

 

[all7holly]

Is that your actual code? If so, the only line being used is the last one. You'd want something like this to test all 3 fields:

strsql = "SELECT * from [tbl sr lab rates] where [lab] ='" & Me.Vendor & "' And [sample description] ='" & Me.MediaType & "' And [labratesTAT] ='" & Me.projectexpenseTAT & "'"

Which assumes all 3 fields are text.

Thank you very much Paul! It worked perfectly!

 

[pbaldy]

Happy to help, and welcome to the site!

 

[all7holly]

Hey Paul,
Since you were a huge help last time I thought I would a new code past you. This one is very complicated to me. I would like this code to save the current report as pdf to specific folder, if this folder already exists, then pdf will be created, if the folder doesn't exist then the folder will be created and then pdf. The name of this report is “rpt work auth”. I would like to folder to be named with the project number and propertyaddress. Then have the report named as “Work Auth”. I would appreciate any help you could give me.


Private Sub Command77_Click()

'Sets report caption name according to the project number
Reports!rpt work auth.Caption = [project number]


Dim myCurrentDir As String
Dim myProjectDir As String
Dim myProjectOutput As String

'Set directories
myCurrentDir = Left(CurrentDb.[ S R Project Database], Len(CurrentDb.[ S R Project Database]) - Len(Dir(CurrentDb. [S R Project Database])))
myInvoiceDir = myCurrentDir & "project number" & "\" & propertyaddress & "\"
myprojectnumberOutput = myprojectnumberDir & txtprojectNumber & ".pdf"

'Check if directory exists
If Len(Dir(myprojectnumberDir, vbDirectory)) = 0 Then
'Make new one if directory doesn't exist
MkDir myprojectnumberDir
Else
'Save report as PDF and set OutputFile to myprojectnumberOutput
DoCmd.OutputTo acOutputReport, "Work Auth", acFormatPDF, myInvoiceOutput, , , , acExportQualityPrint

SendMessage True, myprojectnumberOutput
End If

Else

End If
End Sub

 

[pbaldy]

And what's going wrong with that code? Offhand, logically the OutputTo shouldn't be in the Else, and it appears that the If's and End If's are out of whack, unless you've left something out.

 

[all7holly]

The error I get is…S R Project Database cant fiend the field `|` referred to in your expression. Then it points to… Reports!rptworkauth.Caption = [Project Number]. I had the “End If” in there because originally I was toying with the report also being sent to outlook as a email attachment.

 

[pbaldy]

The report would have to be open to run that line, I suspect. I'd probably set the caption in the open event of the report. I think that would accomplish what you want, though I haven't tested.

 

[all7holly]

I set the event to “on open” and I get the same error message on the same line. I could send you the database if you like.

Private Sub Report_Open(Cancel As Integer)

'Sets report caption name according to the project number
Reports!rptworkauth.Caption = [tbl Work Auth].[Project Number]


Dim myCurrentDir As String
Dim myProjectDir As String
Dim myProjectOutput As String

'Set directories
myCurrentDir = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
myrptworkauthDir = myCurrentDir & "project number" & "\" & PropertyAddress & "\"
myrptworkauthOutput = myrptworkauthDir & [Project Number] & ".pdf"

'Check if directory exists
If Len(Dir(myrptworkauthDir, vbDirectory)) = 0 Then
'Make new one if directory doesn't exist
MkDir myrptworkauthDir

'Save report as PDF and set OutputFile to myprojectnumberOutput
DoCmd.OutputTo acOutputReport, "Work Auth", acFormatPDF, myrptworkauthOutput, , , , acExportQualityPrint



End If
End Sub

 

[pbaldy]

Sorry if I was unclear; the only thing I would have moved is that single line setting the caption. Where is the value? If it's on a form:

Me.Caption = Forms!FormName.ControlName

in a table you'd need to use DLookup or something:

Me.Caption = DLookup(...)