Using Unbound Form Values to Update Table (1 Viewer)

[nasa09]

Hi everyone. I have an unbound form in which a user enters data relating to a credit dispute, and when they hit the Save button I have a sub that is intended to insert that data into a table. This is a bit out of my comfort zone, so I found some code from another forum and adjusted it to my needs. However, I'm getting errors.

The table name is tbl_complaints. The fields from the form are:


txtAcctNumber
txtClient
txtEoscarType
txtEoscarControl
cboMethodReceipt
txtReceivedFrom
txtReceivedDate
txtDateDue
txtResponseDate
cboResponseType
cboDispute1
cboDispute2
txtAddComments
txtNotes
cboProcessor
txtProduct
cboTypeComplaint
cboStatus
cboCode


My code is below.

Private Sub cmdSave_Click()

Dim StrSql As String


On Error GoTo ErrorHandler

StrSql = "INSERT INTO tbl_complaints " _
& "(ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT, RECEIVED_FROM, " _
& "DATE_RECEIVED, DATE_DUE, RESPONSE_DATE, RESPONSE_TYPE, DISPUTE1, DISPUTE2, CLIENT_COMMENTS, NOTES, " _
& "PROCESSOR, PRODUCT, TYPE_OF_COMPLAINT, STATUS, CODE_1 ) " _
    & "VALUES (" _
    & txtAcctNumber & ", '" _
    & txtClient & "', '" _
    & txtEoscarType & "', '" _
    & txtEoscarControl & "', '" _
    & cboMethodReceipt & "', '" _
    & txtReceivedFrom & "', #" _
    & txtReceivedDate & "#, #" _
    & txtDateDue & "#, #" _
    & txtResponseDate & "#, '" _
    & cboResponseType & "', '" _
    & cboDispute1 & "', '" _
    & cboDispute2 & "', '" _
    & txtAddComments & "', '" _
    & txtNotes & "', '" _
    & cboProcessor & "', '" _
    & txtProduct & "', '" _
    & cboTypeComplaint & "', '" _
    & cboStatus & "', " _
    & cboCode & ");"


    DoCmd.SetWarnings False
    DoCmd.RunSQL StrSql, 0
    DoCmd.SetWarnings True

ExitCode:
    Exit Sub

ErrorHandler:
    MsgBox "Error adding record. " & vbCrLf & "Error Number: " & Err.Number & vbCrLf & " Description: " & Err.Description
    Resume ExitCode

End Sub

The error I get is error number 3075, and it says I'm missing an operator in the section that references cboResponseType, but I can't seem to figure out what I'm missing. My head is spinning from staring at these parentheses and apostrophes for so long, so I'm hoping that someone with a bit more expertise in this area can lead me in the right direction.

I appreciate any help you have to offer!

And I'd very much like to avoid a discussion about the evils of unbound forms, but I'll take the tongue-lashing if the result is a working sub.

 

[CJ_London]

is your field RESPONSE_TYPE a number or text? If the form, you need to remove the single quotes

 

[plog]

You are experiencing the evils of unbound forms. My question is why are you avoiding them?

Like you, I'm not going to look through all those lines of code to find your error. Instead, tell me what strSQL resolves to at the end of it all?

Then if that doesn't help, divide and conquer. Start commenting out lines until you find the one that causes the error.

 

[MarkK]

The biggest problem I see is that if someone enters . . .

didn't get the memo

. . . in the txtNotes fields, the apostrophe in "didn't" is going to kill your insert. What I recommend is rather than write the SQL on the fly, create a parameterized QueryDef on the fly, like . . .

[COLOR="Green"]'this is the fixed text of your insert query, shortened here for clarity[/COLOR]
Private Const SQL_INSERT As String = _
   "INSERT INTO tbl_complaints " & _
      "( ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT ) " & _
   "VALUES " & _
      "( p0, p1, p2, p3, p4 )"

Private m_qdf as DAO.QueryDef  [COLOR="Green"]'form global object variable to hold the temp QueryDef[/COLOR]

Property Get InsertQuery as DAO.QueryDef
[COLOR="Green"]'  Exposes the insert query, creating it as required[/COLOR]
   if m_qdf is nothing then set m_qdf = CurrentDb.CreateQueryDef("", SQL_INSERT)
   set InsertQuery = m_qdf
End Property

Private Sub Save_Click()
[COLOR="Green"]'  Executes the insert query with the current data in the form's controls[/COLOR]
   With Me.InsertQuery
      .Parameters(0) = Me.txtAcctNumber
      .Parameters(1) = Me.txtClient
      .Parameters(2) = Me.txtEoscarType
      .Parameters(3) = Me.txtEoscarControl
      .Parameters(4) = Me.cboMethodReceipt
      .Execute dbFailOnError
   End With
End Sub

This way you don't have to muck around with delimiters at all. And your code is generally simpler and more robust.

 

[nasa09]

CJ and Mark, thanks for your help. (To answer CJ, RESPONSE_TYPE is a text field.)

I rewrote the code several times before posting and while I could find the general area of the problem, the actual cause eluded me. Mark's comment made me facepalm, because of course he's right. Thank you so much.

 

[nasa09]

Mark, I tried your ideas and I'm getting error message "Invalid attribute in Sub or Function" from cmdSave_Click.

I obviously made a mistake adjusting your code, but I can't figure out where. Any pointers?

Here's the code I used. And FYI, Code_1 in the table is a boolean, so I added a variable to the Save_Click sub to adjust the value of cboCode1.

Private Const SQL_INSERT As String = _
   "INSERT INTO tbl_complaints " & _
      "( ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT, RECEIVED_FROM, " & _
      "DATE_RECEIVED, DATE_DUE, RESPONSE_DATE, RESPONSE_TYPE, DISPUTE1, DISPUTE2, CLIENT_COMMENTS, NOTES,  " & _
      "PROCESSOR, PRODUCT, TYPE_OF_COMPLAINT, STATUS, CODE_1 ) " & _
   "VALUES " & _
      "( p0, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18 )"


Private m_qdf As DAO.QueryDef  'form global object variable to hold the temp QueryDef

Property Get InsertQuery() As DAO.QueryDef
'  Exposes the insert query, creating it as required
   If m_qdf Is Nothing Then Set m_qdf = CurrentDb.CreateQueryDef("", SQL_INSERT)
   Set InsertQuery = m_qdf
End Property

End Sub

Private Sub Save_Click()

' Changes cboCode to a Boolean
Dim Code1 As Boolean

If cboCode = "Yes" Then
    Code1 = True
Else
    Code1 = False



'  Executes the insert query with the current data in the form's controls
   With Me.InsertQuery
      .Parameters(0) = Me.txtAcctNumber
      .Parameters(1) = Me.txtClient
      .Parameters(2) = Me.txtEoscarType
      .Parameters(3) = Me.txtEoscarControl
      .Parameters(4) = Me.cboMethodReceipt
      .Parameters(5) = Me.txtReceivedFrom
      .Parameters(6) = Me.txtReceivedDate
      .Parameters(7) = Me.txtDateDue
      .Parameters(8) = Me.txtResponseDate
      .Parameters(9) = Me.cboResponseType
      .Parameters(10) = Me.cboDispute1
      .Parameters(11) = Me.cboDispute2
      .Parameters(12) = Me.txtAddComments
      .Parameters(13) = Me.txtNotes
      .Parameters(14) = Me.cboProcessor
      .Parameters(15) = Me.txtProduct
      .Parameters(16) = Me.cboTypeComplaint
      .Parameters(17) = Me.cboStatus
      .Parameters(18) = Code1
      .Execute dbFailOnError
   End With
End Sub

 

[Gasman]

PMFJ,

Not sure if this is the problem, but ....

You are missing End if with your cboCode If statement?

 

[nasa09]

Yikes! You are correct. I fixed that, but the problem remains.:banghead:

 

[MarkK]

A couple of observations . . .

Private Const SQL_INSERT As String = _
   "INSERT INTO tbl_complaints " & _
      "( ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT, RECEIVED_FROM, " & _
      "DATE_RECEIVED, DATE_DUE, RESPONSE_DATE, RESPONSE_TYPE, DISPUTE1, DISPUTE2, CLIENT_COMMENTS, NOTES,  " & _
      "PROCESSOR, PRODUCT, TYPE_OF_COMPLAINT, STATUS, CODE_1 ) " & _
   "VALUES " & _
      "( p0, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18 )"


Private m_qdf As DAO.QueryDef  'form global object variable to hold the temp QueryDef

Property Get InsertQuery() As DAO.QueryDef
'  Exposes the insert query, creating it as required
   If m_qdf Is Nothing Then Set m_qdf = CurrentDb.CreateQueryDef("", SQL_INSERT)
   Set InsertQuery = m_qdf
End Property

[COLOR="Red"]End Sub[/COLOR]  [COLOR="Green"]'this looks wrong[/COLOR]

Private Sub Save_Click()
'  Executes the insert query with the current data in the form's controls
   With Me.InsertQuery
      .Parameters(0) = Me.txtAcctNumber
      .Parameters(1) = Me.txtClient
      .Parameters(2) = Me.txtEoscarType
      .Parameters(3) = Me.txtEoscarControl
      .Parameters(4) = Me.cboMethodReceipt
      .Parameters(5) = Me.txtReceivedFrom
      .Parameters(6) = Me.txtReceivedDate
      .Parameters(7) = Me.txtDateDue
      .Parameters(8) = Me.txtResponseDate
      .Parameters(9) = Me.cboResponseType
      .Parameters(10) = Me.cboDispute1
      .Parameters(11) = Me.cboDispute2
      .Parameters(12) = Me.txtAddComments
      .Parameters(13) = Me.txtNotes
      .Parameters(14) = Me.cboProcessor
      .Parameters(15) = Me.txtProduct
      .Parameters(16) = Me.cboTypeComplaint
      .Parameters(17) = Me.cboStatus
      .Parameters(18) = [COLOR="Red"]cboCode = "Yes"[/COLOR] [COLOR="Green"]'you could just do this[/COLOR]
      .Execute dbFailOnError
   End With
End Sub

 

[MarkK]

What line causes the error, or does the code not run? In either case though, something should be highlighted, either by the compiler or the debugger.

 

[nasa09]

Mark,

To answer your first post, my unfamiliarity with some of this led me to believe that you had omitted an End Sub somewhere, but I obviously goofed. I guess what threw me off was that Sub Save_Click had a header and an End, but I wasn't sure where to put the code you listed above it.

As for what line causes the error, the compiler says it's the very first line, on "Private Const."

 

[CJ_London]

I presume you have

 Option Compare Database
 Option Explicit

right at the top of the module

 

[MarkK]

I think the Const declaration has to occur before any Subs, Functions, or Properties in the module too. Maybe you have other code in that module that we haven't seen?

On further testing, I can cause that error if I put the Private Const declaration inside a Property, Sub or Function. Here's code to cause that error . . .

Private Sub FailsToCompile()
   Private Const ShouldBeModuleLevel As String = "Fail"
End Sub

So as CJ says, you should have this at the top of your module . . .

Option Compare Database
Option Explicit

. . . and then . . .

[COLOR="Green"]' this constant is invisible outside this module, 
' visible to all members of this module
' and cannot exist inside a subroutine.
[/COLOR]Private Const SQL_INSERT As String = _
   "INSERT INTO tbl_complaints " & _
   ...

 

[nasa09]

Ah-ha! That did the trick! Everything seems to be working just fine now.

Mark and CJ, thank you both very, very much for all of your assistance with this. I understand how frustrating it is to deal with someone who hasn't got a clue, and I appreciate that you've taken time out of your day to walk me through it. I've definitely learned some good lessons today that I'll be able to apply to future projects.

 

[MarkK]

I understand how frustrating it is to deal with someone who hasn't got a clue

No frustration here. You presented your problem clearly, implemented our suggestions, and presented developing problems clearly again. That's fun, that's development, that's what I like about it.

Cheers,

 

[speakers_86]

But why not make a bound form with unbound controls? Then just set the me!ControlName = me.txtControlName