Method or data member not found

[hugparty]

Hi kids. I've been staring at this for 2 days trying to figure out why I get the "Method or data member not found" error. I think it's time to let you guys look at it. I'm sure it's something dumb that I can't see for the trees.


Private Sub Command901_Click()
Dim CLAIM_NUMBER, SURNAME, FIRST_NAME, DATE_OF_BIRTH, GENDER As String
Dim strSQLApp As String

CLAIM_NUMBER = Me.HIC_CLAIM_NUMBER
SURNAME = Me.LAST_FULL_NAME
FIRST_NAME = Me.FIRST_FULL_NAME
DATE_OF_BIRTH = Me.BIRTHDATE
GENDER = Me.GENDER
strSQLApp = "INSERT INTO PBP_TRANSACTION_TABLE (CLAIM_NUMBER, SURNAME, FIRST_NAME, DATE_OF_BIRTH, GENDER) VALUES ('" & HIC_CLAIM_NUMBER & "', '" & LAST_FULL_NAME & "','" & FIRST_FULL_NAME & "', '" & BIRTHDATE & "', '" & GENDER & "')"

DoCmd.RunSQL strSQLApp

MsgBox "The Record has been Processed."
End Sub

 

[pbaldy]

What line does it error on? I would guess one of the form references is off.

 

[KenHigg]

Looks like you set the birthday to text and it should be a variant - ? And you may need to wrap it in '#'s - ?

 

[hugparty]

Ok, it highlights yellow on "Private Sub Command901_Click()" but highlights purple on "CLAIM_NUMBER = Me.HIC_CLAIM_NUMBER".

 

[pbaldy]

Then I would guess that HIC_CLAIM_NUMBER is not correct in some way. Make sure that's a control on the form or field in the source data.

 

[hugparty]

It IS a field in the source data, but what do you mean control on the form?

 

[pbaldy]

A textbox, combobox, etc. You can see if

Me!HIC_CLAIM_NUMBER

works.

 

[hugparty]

the ! worked!!!! thank you so much

 

[hugparty]

Ok, it got rid of the error but it is not writing the data to the table. Any ideas?

 

[pbaldy]

Add this before the line running the SQL:

Debug.Print strSQLApp

and check out the final SQL in the VBA Immediate window. If that doesn't reveal the problem, post the SQL here along with the data types of all the fields. Your code treats them all as text, which may not be appropriate, as Ken noted.

 

[hugparty]

k, here it is:


Private Sub Command901_Click()
Dim CLAIM_NUMBER, SURNAME, FIRST_NAME, DATE_OF_BIRTH, GENDER As String
Dim strSQLApp As String

CLAIM_NUMBER = Me!HIC_CLAIM_NUMBER
SURNAME = Me!LAST_NAME
FIRST_NAME = Me!FIRST_FULL_NAME
DATE_OF_BIRTH = Me!BIRTHDATE
GENDER = Me!GENDER

strSQLApp = "INSERT INTO PBP_TRANSACTION_TABLE (CLAIM_NUMBER, SURNAME, FIRST_NAME, GENDER, DATE_OF_BIRTH) VALUES ('" & HIC_CLAIM_NUMBER & "','" & LAST_NAME & "', '" & FIRST_FULL_NAME & "', '" & GENDER & "', '" & BIRTHDATE & "')"

Debug.Print strSQLApp
DoCmd.RunSQL strSQLApp

MsgBox "The Record has been moved to Transaction file."
End Sub


All fields ARE 'text' in the tables. There is no error thrown, it just doesn't copy the info over. The only one that gets copies is "GENDER" for some reason?

 

[pbaldy]

What was printed to the Immediate window?

 

[hugparty]

I tried an experiment - I changed "LAST_NAME" to "SURNAME" in the table properties and it copied THAT data over too. So now I have to figure out why it won't copy unless it is named the same in both tables.

 

[hugparty]

What was printed to the Immediate window?

I'm sorry, what window? It didn't do anything as far as I could tell.

 

[boblarson]

Looks to me that Gender has the same name as the field so it will go in. It also looks like you are setting variables to the values, but then aren't using them as values in the query. It almost looks like you have the variables set to be the same name as the fields which is throwing Access off. Try doing this:


Private Sub Command901_Click()
Dim strSQLApp As String


strSQLApp = "INSERT INTO PBP_TRANSACTION_TABLE (CLAIM_NUMBER, SURNAME, FIRST_NAME, GENDER, DATE_OF_BIRTH) VALUES ('" & Me!HIC_CLAIM_NUMBER & "','" & Me!LAST_NAME & "', '" & Me!FIRST_FULL_NAME & "', '" & Me!GENDER & "', '" & Me!BIRTHDATE & "')"

Debug.Print strSQLApp
DoCmd.RunSQL strSQLApp

MsgBox "The Record has been moved to Transaction file."
End Sub

 

[hugparty]

oh thank god

I CAN GO HOME NOW!!!!



THANK YOU!!!!!

 

[gemma-the-husky]

not sure if this has already been changed, but you cant say this in vba

you cannot type several variables on a single line - you have to type each one separately

in this case gender is a string, but the others are all variants, which may work or may not in your aspplication, but immediately loses all the benefits of data typing

out of interest why would dateofbirth be a string anyway (although its not, its a variant with this sysntax, but your INTENTION was clearly to make it a string)

Dim CLAIM_NUMBER, SURNAME, FIRST_NAME, DATE_OF_BIRTH, GENDER As String