Solved: Some days are better than others.

[ibgreat]

The last error I am getting is a "Method or Data member not found". This is occuring within the Else statement that is saved into strSQL. It occurs in the line

Else strSQL = ....
"SubDisciplineID = '" & Me.txtSubDisciplineID & "', " & _
...

Everything looks like it is spelled right. I did find some errors with the quotes, but I don't see anything else. Perhaps a more fresh or trained eye will catch something else.


Sub SaveCurrentRecord()
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim strSQL As String
If Not rsPerson.BOF And Not rsPerson.EOF Then
'create a new connection instance and open it using the connection string
Set cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection

Dim intCurContact As Integer
intCurContact = 0

'test for blnAddMode value
Debug.Print blnAddMode

'if adding a new record
If blnAddMode = True Then
'create SQL to insert a new record into the database
'containing the values on the form
strSQL = "INSERT INTO tblPerson-D (" & _
"SSNum, LastName, FirstName, " & _
"DOB, DisciplineID, SubDisciplineID, " & _
"AddressHome1, AddressHome2, " & _
"CityHome, StateHome, ZipHome, " & _
"PhoneHome, PhoneCell, FaxHome, E-MailHome) " & _
"VALUES (" & _
"'" & Me.txtSSNum & "', " & _
"'" & Me.txtLastName & "', '" & Me.txtFirstName & "', " & _
"'" & Me.txtDOB & "', " & _
"'" & Me.txtDisciplineID & "', '" & Me.txtSubDisciplineID & "', " & _
"'" & Me.txtAddressHome1 & "', '" & Me.txtAddressHome2 & "', " & _
"'" & Me.txtCityHome & "', " & _
"'" & Me.txtStateHome & "', " & _
"'" & Me.txtZipHome & "', " & _
"'" & Me.txtPhoneHome & "', " & _
"'" & Me.txtPhoneCell & "' " & _
"'" & Me.txtEMailHome & "' " & _
"'" & Me.txtRace & "' " & _
"'" & Me.txtEthnicity & "' " & _
"'" & Me.txtGender & "' " & _
"'" & Me.txtMaritalStatus & "' " & _
"'" & Me.txtReligion & "' " & _
"'" & Me.txtVeteranStatus & "') "

Else
'create SQL to update the existing record in the
'database with the values on the form
strSQL = "UPDATE tblPerson-D SET " & _
"SSNum = '" & Me.txtSSNum & "', " & _
"LastName = '" & Me.txtLastName & "', " & _
"FirstName = '" & Me.txtFirstName & "', " & _
"DOB = '" & Me.txtDOB & "', " & _
"DisciplineID = '" & Me.txtDisciplineID & "', " & _
"SubDisciplineID = '" & Me.txtSubDisciplineID & "', " & _
"AddressHome1 = '" & Me.txtAddress1 & "', " & _
"AddressHome2 = '" & Me.txtAddress2 & "', " & _
"CityHome = '" & Me.txtCity & "', " & _
"StateHome = '" & Me.txtState & "', " & _
"ZipHome = '" & Me.txtZip & "', " & _
"PhoneHome = '" & Me.txtHomePhone & "', " & _
"PhoneCell = '" & Me.txtCellPhone & "', " & _
"FaxHome = '" & Me.txtFaxHome & "', " & _
"EMailHome = '" & Me.txtEMailHome & "', " & _
"Race = '" & Me.txtRace & "', " & _
"Ethnicity = '" & Me.txtEthnicity & "', " & _
"Gender = '" & Me.txtGender & "', " & _
"MaritalStatus = '" & Me.txtMaritalStatus & "', " & _
"Religion = '" & Me.txtReligion & "', " & _
"VeteranStatus = '" & Me.txtVeteranStatus & "', " & _
"WHERE intPersonID = " & rsPerson!intPersonID

'save the id of the current record
intCurContact = rsPerson!PersonID

End If
'set the command to the current connection
Set cmdCommand.ActiveConnection = cnABCdb
'set the insert or update SQL statement to the command text
cmdCommand.CommandText = strSQL
'execute the delete command against the database
cmdCommand.Execute

'while connected to the database, go ahead and
'repopulate the recordset to make sure it contains
'the most current values from the database
Set rsPerson.ActiveConnection = cnABCdb
rsPerson.Requery
Set rsPerson.ActiveConnection = Nothing
'move back to the contact that as current before the requery
If intCurContact > 0 Then
'move bak to the ocntact that as just updated
rsPerson.Find "[intPersonID] = " & intCurContact
Else
'if just added new recored, move thte beginning
'of the recordset
rsPerson.MoveFirst
End If
'reset add mode flag to false
blnAddMode = False
'populate the ocntrols on the form
Call PopulateControlsOnForm
End If
End Sub


Thanks

[CreganTur]

Can we see more of the code? I can't make out anything from the snippet you provided.

[ibgreat]

Sorry, forgot to paste, I changed the original message to include the entire procedure

[CreganTur]

Hmmm...:think:

At first glance I see that you didn't put brackets around your table name (only neccessary because you are using a hyphen)... other than that I really don't see anything right now... but then again it's almost quitting time on a friday, so I'm not seeing much of anything at the moment :whistle:

SQL string are, as I said in another post, very fussy when you use them in VBA. Using underscores to split them across multiple lines is a very tricky business for some reason. I would suggest, for now, put your SQL string on a single line of code and then test it- that way you can tell if the error is in the SQL string itself, or if the error is due to syntax errors in using underscores to make it easier to read.

HTH

[ibgreat]

I fixed the table issue...thanks

I also placed everything in a single string and still got an error in the same location. It is like the object name is wrong, but it isn't.

Actually, even when blnAddMode is True I get the same bat error in the same bat place in Else statement. Why is it even reading the Else statement?

My Debug.print "blnAddMode" & blnAddMode only shows up when the value is true...weird

[Mavyak]

First:
"WHERE intPersonID = " & rsPerson!intPersonID

'save the id of the current record
intCurContact = rsPerson!PersonID
Is this a "intPersonID" vs "PersonID" issue?

Second:
Your commas after field names in the true portion of the if statement end after "Me.txtPhoneCell". Shouldn't all field names be separated by a comma?

Third:
If all field names are supposed be separated bya comma, then you are inserting more fields than you have specified in the insert portion of the SQL.

[Mavyak]

Another thing to look out for when inserting/updating records via VBA is the single tick. Let's say I have a record containing the last name: O'Brian. When you concatenate that in VBA it looks like: 'O'Brian'. To SQL, the value is 'O' with jibberish afterward. When constructing strings in VBA to pass on to SQL, always use the replace function to double up single ticks liek so:
strSQL = "INSERT INTO TABLE(LASTNAME) VALUES('" & Replace(myvariable, "'", "''") & "')"

[ibgreat]

Great catches....Thank you. I think I made all the updates, I'm going to take another look now.

(I actually thought I had looked at all those things...I must be getting tired.)

But, still the same issue. Here is the updated code.

Sub SaveCurrentRecord()
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim strSQL As String
If Not rsPerson.BOF And Not rsPerson.EOF Then
'create a new connection instance and open it using the connection string
Set cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection

Dim intCurContact As Integer
intCurContact = 0

'test for blnAddMode value
Debug.Print blnAddMode

'if adding a new record
If blnAddMode = True Then
'create SQL to insert a new record into the database
'containing the values on the form
strSQL = "INSERT INTO [tblPerson_D] (" & _
"SSNum, LastName, FirstName, " & _
"DOB, DisciplineID, SubDisciplineID, " & _
"AddressHome1, AddressHome2, " & _
"CityHome, StateHome, ZipHome, "
strSQL = strSQL + "PhoneHome, PhoneCell, FaxHome, EMailHome, " & _
"Race, Ethnicity, Gender, MaritalStatus, Religion, VeteranStatus)"
strSQL = strSQL + "VALUES (" & _
"'" & Me.txtSSNum & "', " & _
"'" & Me.txtLastName & "', " & _
"'" & Me.txtFirstName & "', " & _
"'" & Me.txtDOB & "', " & _
"'" & Me.txtDisciplineID & "', '" & _
"'" & Me.txtSubDisciplineID & "', "
strSQL = strSQL + "'" & Me.txtAddressHome1 & "', " & _
"'" & Me.txtAddressHome2 & "', " & _
"'" & Me.txtCityHome & "', " & _
"'" & Me.txtStateHome & "', " & _
"'" & Me.txtZipHome & "', " & _
"'" & Me.txtPhoneHome & "', " & _
"'" & Me.txtPhoneCell & "', " & _
"'" & Me.txtFaxHome & "', " & _
"'" & Me.txtEMailHome & "', " & _
strSQL = strSQL + "'" & Me.txtRace & "', " & _
"'" & Me.txtEthnicity & "', " & _
"'" & Me.txtGender & "', " & _
"'" & Me.txtMaritalStatus & "', " & _
"'" & Me.txtReligion & "', " & _
"'" & Me.txtVeteranStatus & "') "

Else
'create SQL to update the existing record in the
'database with the values on the form
strSQL = "UPDATE [tblPerson_D] SET " & _
"SSNum = '" & Me.txtSSNum & "', " & _
"LastName = '" & Me.txtLastName & "', " & _
"FirstName = '" & Me.txtFirstName & "', " & _
"DOB = '" & Me.txtDOB & "', "
Debug.Print "strSQL = " & strSQL
strSQL = strSQL + "DisciplineID = '" & Me.txtDisciplineID & "', " & _
"SubDisciplineID = '" & Me.txtSubDisciplineID & "', " & _
"AddressHome1 = '" & Me.txtAddress1 & "', " & _
"AddressHome2 = '" & Me.txtAddress2 & "', " & _
"CityHome = '" & Me.txtCity & "', " & _
"StateHome = '" & Me.txtState & "', " & _
"ZipHome = '" & Me.txtZip & "', "
Debug.Print "strSQL = " & strSQL
strSQL = strSQL + "PhoneHome = '" & Me.txtHomePhone & "', " & _
"PhoneCell = '" & Me.txtCellPhone & "', " & _
"FaxHome = '" & Me.txtFaxHome & "', " & _
"EMailHome = '" & Me.txtEMailHome & "', "
Debug.Print "strSQL = " & strSQL
strSQL = strSQL + "Race = '" & Me.txtRace & "', " & _
"Ethnicity = '" & Me.txtEthnicity & "', " & _
"Gender = '" & Me.txtGender & "', " & _
"MaritalStatus = '" & Me.txtMaritalStatus & "', " & _
"Religion = '" & Me.txtReligion & "', " & _
"VeteranStatus = '" & Me.txtVeteranStatus & "', "
Debug.Print "strSQL = " & strSQL
strSQL = strSQL + "WHERE intPersonID = " & rsPerson!intPersonID
Debug.Print "strSQL = " & strSQL

'save the id of the current record
intCurContact = rsPerson!PersonID

End If
'set the command to the current connection
Set cmdCommand.ActiveConnection = cnABCdb
'set the insert or update SQL statement to the command text
cmdCommand.CommandText = strSQL
'execute the delete command against the database
cmdCommand.Execute

'while connected to the database, go ahead and
'repopulate the recordset to make sure it contains
'the most current values from the database
Set rsPerson.ActiveConnection = cnABCdb
rsPerson.Requery
Set rsPerson.ActiveConnection = Nothing
'move back to the contact that as current before the requery
If intCurContact > 0 Then
'move bak to the ocntact that as just updated
rsPerson.Find "[PersonID] = " & intCurContact
Else
'if just added new recored, move thte beginning
'of the recordset
rsPerson.MoveFirst
End If
'reset add mode flag to false
blnAddMode = False
'populate the ocntrols on the form
Call PopulateControlsOnForm
End If
End Sub

[ibgreat]

When constructing strings in VBA to pass on to SQL, always use the replace function to double up single ticks

Good to know, doesn't apply with my current recordset (but it could).
Are you using two single apostophes (ticks) or a quote mark? If it is a single apostophe, wouldn't SQL still read it at as O with another field following or does it know better because there is a comma? If you use the quote mark doesn't this change it to O"Brian within the db.
Using my current code would it look like this?

strSQL = "INSERT INTO [tblPerson_D] (" & _
"SSNum, LastName, FirstName ")" & _
"VALUES ('" & Replace (Me.txtSSNum, "'", "''") & "', " & _
"'" & Replace (Me.txtLastName & "'", "''") & "'," & _
"'" & Replace (Me.txtFirstName & "'", "''") & "')"

[Mavyak]

Using my current code would it look like this?

strSQL = "INSERT INTO [tblPerson_D] (" & _
"SSNum, LastName, FirstName ")" & _
"VALUES ('" & Replace (Me.txtSSNum, "'", "''") & "', " & _
"'" & Replace (Me.txtLastName & "'", "''") & "'," & _
"'" & Replace (Me.txtFirstName & "'", "''") & "')"
Yes.

I'm still a little confused by this code:
strSQL = strSQL + "WHERE intPersonID = " & rsPerson!intPersonID
Debug.Print "strSQL = " & strSQL

'save the id of the current record
intCurContact = rsPerson!PersonID
Are there really two fields in your recordset named "intPersonID" and "PersonID"?

[ibgreat]

ah, I changed it elsewhere but still missed that one, I changed it to:



Debug.Print strSQL
strSQL = strSQL + "WHERE [PersonID] = " & rsPerson!PersonID

Thanks again, any ideas on the data mismatch in the else statement on the following line????


"SubDisciplineID = '" & Me.txtSubDisciplineID & "', " & _

[Mavyak]

What is the data type of the "SubDisciplineID" in your table? You are putting quotes around it like it is a string. Most ID fields I've encountered are numeric.

[ibgreat]

Yes, subDisciplineID is an integer in the table.

But because it is being saved in strSQL I'm pretty should it should be saved as a string. When cmdcommand.execute is run it will be run with the SQL string not an integer. Such as:

VeteranStatus = 0 WHERE [PersonID] = 1

Otherwise, the SQL would look like:

VeteranStatus = 0 WHERE 3 = 1

Let me know if I'm crazy. I'll check in tomorrow.

No, thoughts on the datatype mismatch?

[Mavyak]

Change this:
"SubDisciplineID = '" & Me.txtSubDisciplineID & "', " & _

to this:
"SubDisciplineID = " & Me.txtSubDisciplineID & ", " & _

[ibgreat]

My apologies, sometimes I have a hard time following directions. Got confused with the prior post. I think I got it this time ;) . I made the changes in the If and Else statements, but still the error.

Sub SaveCurrentRecord()
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim strSQL As String
If Not rsPerson.BOF And Not rsPerson.EOF Then
'create a new connection instance and open it using the connection string
Set cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection

Dim intCurContact As Integer
intCurContact = 0

'test for blnAddMode value
Debug.Print blnAddMode

'if adding a new record
If blnAddMode = True Then
'create SQL to insert a new record into the database
'containing the values on the form
strSQL = "INSERT INTO [tblPerson_D] (" & _
"SSNum, LastName, FirstName, " & _
"DOB, DisciplineID, SubDisciplineID, " & _
"AddressHome1, AddressHome2, " & _
"CityHome, StateHome, ZipHome, "
strSQL = strSQL + "PhoneHome, PhoneCell, FaxHome, EMailHome, " & _
"Race, Ethnicity, Gender, MaritalStatus, Religion, VeteranStatus)"
strSQL = strSQL + "VALUES (" & _
"'" & Me.txtSSNum & "', " & _
"'" & Me.txtLastName & "', " & _
"'" & Me.txtFirstName & "', " & _
"'" & Me.txtDOB & "', " & _
Me.txtDisciplineID & ", " & _
Me.txtSubDisciplineID & ", "
strSQL = strSQL + "'" & Me.txtAddressHome1 & "', " & _
"'" & Me.txtAddressHome2 & "', " & _
"'" & Me.txtCityHome & "', " & _
"'" & Me.txtStateHome & "', " & _
"'" & Me.txtZipHome & "', " & _
"'" & Me.txtPhoneHome & "', " & _
"'" & Me.txtPhoneCell & "', " & _
"'" & Me.txtFaxHome & "', " & _
"'" & Me.txtEMailHome & "', " & _
strSQL = strSQL + "'" & Me.txtRace & "', " & _
"'" & Me.txtEthnicity & "', " & _
"'" & Me.txtGender & "', " & _
"'" & Me.txtMaritalStatus & "', " & _
"'" & Me.txtReligion & "', " & _
"'" & Me.txtVeteranStatus & "') "

Else
'create SQL to update the existing record in the
'database with the values on the form
strSQL = "UPDATE [tblPerson_D] SET " & _
"SSNum = '" & Me.txtSSNum & "', " & _
"LastName = '" & Me.txtLastName & "', " & _
"FirstName = '" & Me.txtFirstName & "', " & _
"DOB = '" & Me.txtDOB & "', "
Debug.Print strSQL
'removed the apostrophe from Me.txtDisciplineID and Me.txtSubDisciplineID
strSQL = strSQL + "DisciplineID = " & Me.txtDisciplineID & ", " & _
"SubDisciplineID = " & Me.txtSubDisciplineID & ", " & _
"AddressHome1 = '" & Me.txtAddress1 & "', " & _
"AddressHome2 = '" & Me.txtAddress2 & "', " & _
"CityHome = '" & Me.txtCity & "', " & _
"StateHome = '" & Me.txtState & "', " & _
"ZipHome = '" & Me.txtZip & "', "
Debug.Print strSQL
strSQL = strSQL + "PhoneHome = '" & Me.txtHomePhone & "', " & _
"PhoneCell = '" & Me.txtCellPhone & "', " & _
"FaxHome = '" & Me.txtFaxHome & "', " & _
"EMailHome = '" & Me.txtEMailHome & "', "
Debug.Print strSQL
strSQL = strSQL + "Race = '" & Me.txtRace & "', " & _
"Ethnicity = '" & Me.txtEthnicity & "', " & _
"Gender = '" & Me.txtGender & "', " & _
"MaritalStatus = '" & Me.txtMaritalStatus & "', " & _
"Religion = '" & Me.txtReligion & "', " & _
"VeteranStatus = '" & Me.txtVeteranStatus & "', "
Debug.Print strSQL
strSQL = strSQL + "WHERE [PersonID] = " & rsPerson!PersonID
Debug.Print strSQL

'save the id of the current record
intCurContact = rsPerson!PersonID

End If
'set the command to the current connection
Set cmdCommand.ActiveConnection = cnABCdb
'set the insert or update SQL statement to the command text
cmdCommand.CommandText = strSQL
'execute the delete command against the database
cmdCommand.Execute

'while connected to the database, go ahead and
'repopulate the recordset to make sure it contains
'the most current values from the database
Set rsPerson.ActiveConnection = cnABCdb
rsPerson.Requery
Set rsPerson.ActiveConnection = Nothing
'move back to the contact that as current before the requery
If intCurContact > 0 Then
'move back to the contact that was just updated
rsPerson.Find "[PersonID] = " & intCurContact
Else
'if just added new recored, move to the beginning
'of the recordset
rsPerson.MoveFirst
End If
'reset add mode flag to false
blnAddMode = False
'populate the ocntrols on the form
Call PopulateControlsOnForm
End If
End Sub

[Mavyak]

You should check all the data types of the fields you are dealing with. Numeric fields should be sent raw. Text/string fileds should be encased in single quotes. Dates, for MS Access should be encased in pound signs (#). Be sure you are validating dates and numbers that you allow users to free-type in a textbox. If you have a date field and a user types "Fred" into the textbox, your code doesn't know the difference and will try to insert #Fred# into your table on the back-end unless you write in some data validation code to ensure the value the user entered is really a date.

One other question, where is blnAddMode populated? Is that a global variable? Is it explicitly declared as a Boolean?

[ibgreat]

I was thinking we at the point where I would need to go back again and take look at the fields. I had done this a couple of time early on, but it does seem another check is in order.

At this point I wasn't entering data via the form into anything but the FirstName and LastName fields to ensure this wasn't the issue. I will double check in a bit and post again this afternoon with an update.

Thanks again for all the help. I was doing some looking around and realized how frequently you post on multiple forums. I am sure that like myself, you contributions are greatly appreciated by others!!

[ibgreat]

Just wanted to share I got it working. The best I could figure out is that because I was using text boxes on the form it was reading the data as a string. I used the val() function to force the dataype in my foreign key fields.

I just wanted to post an update and thank everyone!!!:clap:

[Mavyak]

Huzzah! :beerchug: