Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-09-2009, 09:26 AM   #1
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Method or data member not found

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

hugparty is offline   Reply With Quote
Old 04-09-2009, 09:34 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,131
Thanks: 13
Thanked 4,080 Times in 4,015 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Method or data member not found

What line does it error on? I would guess one of the form references is off.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 04-09-2009, 09:35 AM   #3
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,309
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Re: Method or data member not found

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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 04-09-2009, 09:37 AM   #4
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Re: Method or data member not found

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

hugparty is offline   Reply With Quote
Old 04-09-2009, 09:43 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,131
Thanks: 13
Thanked 4,080 Times in 4,015 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Method or data member not found

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 04-09-2009, 10:05 AM   #6
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Re: Method or data member not found

It IS a field in the source data, but what do you mean control on the form?
hugparty is offline   Reply With Quote
Old 04-09-2009, 10:10 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,131
Thanks: 13
Thanked 4,080 Times in 4,015 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Method or data member not found

A textbox, combobox, etc. You can see if

Me!HIC_CLAIM_NUMBER

works.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 04-09-2009, 10:35 AM   #8
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Re: Method or data member not found

the ! worked!!!! thank you so much
hugparty is offline   Reply With Quote
Old 04-09-2009, 03:13 PM   #9
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Re: Method or data member not found

Ok, it got rid of the error but it is not writing the data to the table. Any ideas?
hugparty is offline   Reply With Quote
Old 04-09-2009, 03:23 PM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,131
Thanks: 13
Thanked 4,080 Times in 4,015 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Method or data member not found

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 04-09-2009, 03:35 PM   #11
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Re: Method or data member not found

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?
hugparty is offline   Reply With Quote
Old 04-09-2009, 03:38 PM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,131
Thanks: 13
Thanked 4,080 Times in 4,015 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Method or data member not found

What was printed to the Immediate window?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 04-09-2009, 03:42 PM   #13
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Re: Method or data member not found

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 is offline   Reply With Quote
Old 04-09-2009, 03:43 PM   #14
hugparty
Registered User
 
Join Date: Mar 2009
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
hugparty is on a distinguished road
Re: Method or data member not found

Quote:
Originally Posted by pbaldy View Post
What was printed to the Immediate window?
I'm sorry, what window? It didn't do anything as far as I could tell.
hugparty is offline   Reply With Quote
Old 04-09-2009, 03:44 PM   #15
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,827 Times in 1,578 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Method or data member not found

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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
method or data member not found option Excel 6 11-05-2008 09:10 AM
'Method or data member not found' error gaju_3112 Forms 1 05-04-2008 09:47 PM
method or data member not found sandy6078 Modules & VBA 7 04-11-2008 04:53 AM
Method or Data Member not found error CarlyS Reports 21 10-03-2006 04:08 AM
[SOLVED] How to make Pivot-like report with data in columns anicoll Reports 0 05-08-2003 12:52 PM




All times are GMT -8. The time now is 10:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World