N
night_writer
This is probably not the right newgroup for this question except that
I click a button on a form to get into trouble here...and you've been
so helpful in the past that I hope someone can help me now, or direct
me to a better newsgroup.
I have a form that lists all 50 states as checkboxes. They have a Tag
property of "State". The form includes a project number, and I test
each state to determine if there is an activity (a filing) has been
performed for that state and project number. If a box is checked but
no filing has been made, I put a solid border around the label that
shows the state name.
What I want to do now is give the users a button to click to create a
record for tblFilings if the state name has a solid border. I have
some example code from a data base someone else made, but I'm not sure
how good it is, and there are some differences.
Here is my code:
~~~~~~~~~~~~~~~
Dim ctl As Control
Dim newTitle As String, newLOB As String, newFilingType As String
Dim newState As String, newAnalyst As String, newPropDate As Date
Dim newProjectID As Long, strSQL As String
newProjectID = Me.ProjectID
newTitle = Me.strTitle
newLOB = Me.strLOBDescription
newFilingType = Me.strFilingType
newPropDate = Me.dteProposedEff
For Each ctl In Me.Controls
If ctl.Tag = "State" Then
Select Case ctl.ControlType
Case acLabel
'BorderStyle=1: no filing yet exists for same ProjectID and
state
If ctl.BorderStyle = 1 Then
'Extract state in form XX from control name in form
ctlXX_Label
newState = Mid(ctl.Name, 4, 2)
'Selection of analyst depends on state and LOB
newAnalyst = DLookup("[strAnalyst]", "qryAnalystMatrix", _
"[strLOBDescription] = '" & _
Me.strLOBDescription & "' AND [State] = '" & newState
& "'")
strSQL = "INSERT INTO tblFilings ([ProjectID], [strTitle],
[strLOBDescription], [strState], [strFilingType], [strAnalyst],
[dteISOProposedEff]) " & _
"VALUES (" & newProjectID & ",'" & newTitle & "','" &
_
newLOB & "','" & newState & "','" & newFilingType & _
"','" & newAnalyst & "','" & newPropDate & "')"
MsgBox strSQL
CurrentDb.Execute strSQL
With ctl
.BorderStyle = 0
.BorderColor = 0
.BorderWidth = 0
End With
End If
End Select
End If
Next ctl
~~~~~~~~~~~~~~~~~
my MsgBox informs me that my strSQL =
INSERT INTO tblFilings ([ProjectID], [strTitle], [strLOBDescription],
[strState], [strFilingType], [strAnalyst], [dteISOProposedEff]) VALUES
(648,'Multistate rules and forms revision Add'l Insd.','General
Liability','TX','Rule / Form','Smith,Jane','6/1/2005')
I get an error that states:
Syntax error (missing operator) in query expression "Multistate rules
and forms revision Add'l Insd.','General liability','TX','Rule /
Form','Smith',
I suspect that the problem might come from either (or both) the
apostrophy in the string title (Add'l) or the comma in the strAnalyst
(Smith,Jane) .
There are also two fields that aren't strings: ProjectID, which is
being sent with no apostrophies and newPropDate which is being sent
like a string but it's actually a date.
I am also not sure how the "CurrentDb.Execute strSQL" fits in. I
pulled it from similar code in another data base where it seems to
work, but if my research is correct, I should be using "DoCmd.RunSQL
strSQL".
I have changed and tweaked everything I can think of, but am still
getting the error messages. I would greatly appreciate any help or
suggestions anyone has to offer.
Thanks!
Alice
I click a button on a form to get into trouble here...and you've been
so helpful in the past that I hope someone can help me now, or direct
me to a better newsgroup.
I have a form that lists all 50 states as checkboxes. They have a Tag
property of "State". The form includes a project number, and I test
each state to determine if there is an activity (a filing) has been
performed for that state and project number. If a box is checked but
no filing has been made, I put a solid border around the label that
shows the state name.
What I want to do now is give the users a button to click to create a
record for tblFilings if the state name has a solid border. I have
some example code from a data base someone else made, but I'm not sure
how good it is, and there are some differences.
Here is my code:
~~~~~~~~~~~~~~~
Dim ctl As Control
Dim newTitle As String, newLOB As String, newFilingType As String
Dim newState As String, newAnalyst As String, newPropDate As Date
Dim newProjectID As Long, strSQL As String
newProjectID = Me.ProjectID
newTitle = Me.strTitle
newLOB = Me.strLOBDescription
newFilingType = Me.strFilingType
newPropDate = Me.dteProposedEff
For Each ctl In Me.Controls
If ctl.Tag = "State" Then
Select Case ctl.ControlType
Case acLabel
'BorderStyle=1: no filing yet exists for same ProjectID and
state
If ctl.BorderStyle = 1 Then
'Extract state in form XX from control name in form
ctlXX_Label
newState = Mid(ctl.Name, 4, 2)
'Selection of analyst depends on state and LOB
newAnalyst = DLookup("[strAnalyst]", "qryAnalystMatrix", _
"[strLOBDescription] = '" & _
Me.strLOBDescription & "' AND [State] = '" & newState
& "'")
strSQL = "INSERT INTO tblFilings ([ProjectID], [strTitle],
[strLOBDescription], [strState], [strFilingType], [strAnalyst],
[dteISOProposedEff]) " & _
"VALUES (" & newProjectID & ",'" & newTitle & "','" &
_
newLOB & "','" & newState & "','" & newFilingType & _
"','" & newAnalyst & "','" & newPropDate & "')"
MsgBox strSQL
CurrentDb.Execute strSQL
With ctl
.BorderStyle = 0
.BorderColor = 0
.BorderWidth = 0
End With
End If
End Select
End If
Next ctl
~~~~~~~~~~~~~~~~~
my MsgBox informs me that my strSQL =
INSERT INTO tblFilings ([ProjectID], [strTitle], [strLOBDescription],
[strState], [strFilingType], [strAnalyst], [dteISOProposedEff]) VALUES
(648,'Multistate rules and forms revision Add'l Insd.','General
Liability','TX','Rule / Form','Smith,Jane','6/1/2005')
I get an error that states:
Syntax error (missing operator) in query expression "Multistate rules
and forms revision Add'l Insd.','General liability','TX','Rule /
Form','Smith',
I suspect that the problem might come from either (or both) the
apostrophy in the string title (Add'l) or the comma in the strAnalyst
(Smith,Jane) .
There are also two fields that aren't strings: ProjectID, which is
being sent with no apostrophies and newPropDate which is being sent
like a string but it's actually a date.
I am also not sure how the "CurrentDb.Execute strSQL" fits in. I
pulled it from similar code in another data base where it seems to
work, but if my research is correct, I should be using "DoCmd.RunSQL
strSQL".
I have changed and tweaked everything I can think of, but am still
getting the error messages. I would greatly appreciate any help or
suggestions anyone has to offer.
Thanks!
Alice