Method or data member not found (1 Viewer)

hugparty

Registered User.
Local time
Today, 06:26
Joined
Mar 17, 2009
Messages
21
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

Wino Moderator
Staff member
Local time
Today, 06:26
Joined
Aug 30, 2003
Messages
36,118
What line does it error on? I would guess one of the form references is off.
 

KenHigg

Registered User
Local time
Today, 09:26
Joined
Jun 9, 2004
Messages
13,327
Looks like you set the birthday to text and it should be a variant - ? And you may need to wrap it in '#'s - ?
 

hugparty

Registered User.
Local time
Today, 06:26
Joined
Mar 17, 2009
Messages
21
Ok, it highlights yellow on "Private Sub Command901_Click()" but highlights purple on "CLAIM_NUMBER = Me.HIC_CLAIM_NUMBER".

:confused:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:26
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 06:26
Joined
Mar 17, 2009
Messages
21
It IS a field in the source data, but what do you mean control on the form?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:26
Joined
Aug 30, 2003
Messages
36,118
A textbox, combobox, etc. You can see if

Me!HIC_CLAIM_NUMBER

works.
 

hugparty

Registered User.
Local time
Today, 06:26
Joined
Mar 17, 2009
Messages
21
Ok, it got rid of the error but it is not writing the data to the table. Any ideas?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:26
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 06:26
Joined
Mar 17, 2009
Messages
21
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

Wino Moderator
Staff member
Local time
Today, 06:26
Joined
Aug 30, 2003
Messages
36,118
What was printed to the Immediate window?
 

hugparty

Registered User.
Local time
Today, 06:26
Joined
Mar 17, 2009
Messages
21
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.
 

boblarson

Smeghead
Local time
Today, 06:26
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 06:26
Joined
Mar 17, 2009
Messages
21
oh thank god

I CAN GO HOME NOW!!!!



THANK YOU!!!!!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:26
Joined
Sep 12, 2006
Messages
15,614
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
 

Users who are viewing this thread

Top Bottom