Syntax to reference form field from VBA? (1 Viewer)

[WinDancer]

I am trying to select one specific record from an open form and use that single value as the basis for running more code against only that single record.

I keep getting a "Data type mismatch in criteria expression" error-

anyone see a problem with this?

DoCmd.ApplyFilter , "SurveyNumber = 'forms!frmTabSurvey!SurveyNumber'"

SurveyNumber is an autonumber field...

Thanks,
Dave

 

[dkinley]

You can try ...

DoCmd.ApplyFilter , "SurveyNumber = " & Forms!frmTabSurvey!SurveyNumber

-dK

 

[WinDancer]

That works- thank you very much Back to work,
Dave

 

[dkinley]

Hehehe .. sorry to put ya back to work - but glad the solution hit on first post.

Good luck!
-dK

 

[WinDancer]

That was in my first few lines of code-
Now I am at the end of my code and it hates my syntax again-
Trying to send an email to the email address on the forrm....

myItem.to = Forms!frmTabSurvey![Employer E-Email]

Thanks,
Dave

 

[dkinley]

I am not sure what "myItem.to" means. I am assuming you have declared something and possibly using a With block.

I am assuming an e-mail function that uses something like ...

Dim myolApp As Outlook.Application
Dim myItem As Object
 
Set myolApp = CreateObject("Outlook.Application")
Set myItem = myolApp.CreateItem(olMailItem)
 
myItem.To = "Email Address"
myItem.Subject = "Email Subject"
myItem.Body = "Email Body Text"
myItem.DeleteAfterSubmit = True
myItem.Send

The syntax = Forms!.... all looks okay to me. Are you sure this is the right reference for the form control - the correct name? I think the above should work, could you do a stare and compare to make sure that the appropriate statements are used?

I got to thinking, replace the reference by something in text ... = "test" and see what happens to make sure that the application/object references are working and being called correctly. If so, then there is probably something up with the syntax or the correct naming.

-dK

 

[WinDancer]

Private Sub Command879_Click()
On Error GoTo Err_Command879_Click
'Isolate the correct Survey, by Survey Number
DoCmd.ApplyFilter , "SurveyNumber = " & Forms!frmTabSurvey!SurveyNumber
Pause (5)
'Kill screen updates
Call acbShowUpdates(False)
Pause (1)

'Convert each of the 4 form pages to four PDF formatted documents with fixed names
'and store those 4 fixed-name docs to the user's hard drive C:\Windows\Temp

Forms!frmTabSurvey!Page1.SetFocus
DoCmd.OutputTo acOutputForm, "frmTabSurvey", _
acFormatPDF, "C:\Windows\Temp\ElectronicSurveyPage1.pdf", False

Forms!frmTabSurvey!Page2.SetFocus
DoCmd.OutputTo acOutputForm, "frmTabSurvey", _
acFormatPDF, "C:\Windows\Temp\ElectronicSurveyPage2.pdf", False

Forms!frmTabSurvey!Page3.SetFocus
DoCmd.OutputTo acOutputForm, "frmTabSurvey", _
acFormatPDF, "C:\Windows\Temp\ElectronicSurveyPage3.pdf", False

Forms!frmTabSurvey!Page4.SetFocus
DoCmd.OutputTo acOutputForm, "frmTabSurvey", _
acFormatPDF, "C:\Windows\Temp\ElectronicSurveyPage4.pdf", False


Pause (2)

'Move the user back to page 1
Forms!frmTabSurvey!Page1.SetFocus

'Activate screen updates
Call acbShowUpdates(True)


'Pause (1)

'MsgBox "The Survey documents have been created"

Pause (3)

'create the email here
MsgBox "Starting email routine now"

Set MyOlApp = CreateObject("Outlook.Application")
MsgBox "Created Object"
Set myItem = MyOlApp.CreateItem(O1MailItem)
MsgBox "Set item as email"
myItem.Subject = "A copy of the survey you requested"
MsgBox "Add the email subject"
myItem.Body = "All 4 pages of your recent Rate Classification Survey are attached. Please let me know if I can help with anything else."
Set MyAttachments = myItem.Attachments
'myattachments.Add "c:\vwtemp\" & CntyName(x) & ".pdf" 'This line you repeat for each additional attach
MsgBox "start adding the attachments"
MyAttachments.Add "C:\Windows\Temp\ElectronicSurveyPage1.pdf" '& ".pdf"
MyAttachments.Add "C:\Windows\Temp\ElectronicSurveyPage2.pdf" '& ".pdf"
MyAttachments.Add "C:\Windows\Temp\ElectronicSurveyPage3.pdf" '& ".pdf"
MyAttachments.Add "C:\Windows\Temp\ElectronicSurveyPage4.pdf" '& ".pdf"
MsgBox "attachments added"
'DoCmd.ApplyFilter , "SurveyNumber = " & Forms!frmTabSurvey!SurveyNumber
myItem.to = Forms!frmTabSurvey!!TabCtl0![Employer E-Email]
'myItem.to = Forms!frmTabSurvey![employer email] 'Rst1.Fields("email") 'Here is the email address
'myItem.deleteaftersubmit = True 'don't keep a copy in the sent file

myItem.Send



exit_command879_click:
Exit Sub

Err_Command879_Click:
MsgBox err.Description
Resume exit_command879_click

End Sub

I added the tab number thinking that may be it, but no- just a different error message about lets and gets

 

[dkinley]

Just for grins ... try 2 things.

First, try using: myItem.To = "TEST"

Execute and see if 'TEST' is in the subject line. If it is AND if this sub is on the same form as the [Employer E-Email], try using:

myItem.To = Me.[Employer E-Email]

-dK

 

[MSAccessRookie]

Just for grins ... try 2 things.

First, try using: myItem.To = "TEST"

Execute and see if 'TEST' is in the subject line. If it is AND if this sub is on the same form as the [Employer E-Email], try using:

myItem.To = Me.[Employer E-Email]

-dK

Are you using "Option Explicit" in your VB Module? I do not see any declarations for your variables? Somethiong similar to what dkinley said in post #6 is what I would have expected to find. "Option Explicit" would make you define each variable before it could be used.