Compile Error: Method or Data Member Not Found

[JillianM]

Ok, please forgive me. I know very little about coding, but this same code works perfectly in another db I am using. I am trying to update several fields in a form based on the selection of a combo box, in this case Combo53. I set up the code and ran it and get an error stating "Compile Error: Method or Data Member Not Found" with the Private Sub Combo53_AfterUpdate()highlighted in yellow.

Private Sub Combo53_AfterUpdate()
Me.FirstName = Me.Combo53.Column(1)
Me.Profession = Me.Combo53.Column(2)
Me.NPI = Me.Combo53.Column(3)
Me.LIC = Me.Combo53.Column(4)
Me.ADDRESS1 = Me.Combo53.Column(5)
Me.Address2 = Me.Combo53.Column(6)
Me.City = Me.Combo53.Column(7)
Me.State = Me.Combo53.Column(8)
Me.Zip = Me.Combo53.Column(9)
Me.EmailAdd = Me.Combo53.Column(10)
Me.Phone = Me.Combo53.Column(11)
End Sub

At another suggestion, I also added "End With" before End Sub but I am getting the same error.
Any tips or suggestions? Thanks!

 

[dhookom]

Nothing in your code suggests you need "End With". Did you attempt to compile your code? Does your combo53 Row Source actually return 12 columns?

BTW: Do yourself a favor and rename Combo53 to something that makes sense.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

What happens if you start with column 0?

Randy

 

[JillianM]

I know how annoying it must be to help the newbies, but like my original post said, I really don't know anything about coding. The codes that I have been able to compile thus far have been by piecing together material I have found online. I did change my Combo Box Name. The code now reads:

Private Sub LastNameComboBox_AfterUpdate()
Me.FirstName = .Column(1)
Me.Profession = .Column(2)
Me.NPI = .Column(3)
Me.LIC = .Column(4)
Me.ADDRESS1 = .Column(5)
Me.Address2 = .Column(6)
Me.City = .Column(7)
Me.State = .Column(8)
Me.Zip = .Column(9)
Me.EmailAdd = .Column(10)
Me.Phone = .Column(11)
End Sub

There are a total of 13 columns in my combo box. My form updates a linked table. That wouldn't have anything to do with the issue, would it? I've attached the linked spreadsheet and accessdb to this post. Thanks!

 

[JillianM]

Randy, Column 0 is the last name field which stays in the combo box when the appropriate doctor is selected.

 

[randy700]

What is the record source for your form?
What is the control source for your combo box?
Do you have a text box name FirstName?


Randy

 

[JillianM]

My database is posted above, but the record source for my form is a linked table titled Tracy_Lab. The combo box is pulling from another table titled DrList which gives a drop down showing almost all of the other fields on the page. I am trying to write my code to automatically update the other fields, but save the information from the fields to the Tracy_Lab table. My other fields are all from Tracy_Lab and are titled as follows:
FirstName
Profession
NPI
LIC
ADDRESS1
Address2
City
State
Zip
EmailAdd
Phone

Thanks!

 

[dhookom]

Your code with only .Column(x) would need to be:

Private Sub LastNameComboBox_AfterUpdate()
[b] With Me.lastnameComboBox[/b]
  Me.FirstName = .Column(1)
  Me.Profession = .Column(2)
  Me.NPI = .Column(3)
  Me.LIC = .Column(4)
  Me.ADDRESS1 = .Column(5)
  Me.Address2 = .Column(6)
  Me.City = .Column(7)
  Me.State = .Column(8)
  Me.Zip = .Column(9)
  Me.EmailAdd = .Column(10)
  Me.Phone = .Column(11)
[b] End With[/b]
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Are you actually attempting to store all of the values from DrList into Tracy_Lab? This is generally considered bad practice. Typically, the primary key value from DrList should be the only value from DrList stored in Tracy_Lab.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

several fields in a form

What are those fields?
Are they all Labels? If so, you may want to:

Private Sub LastNameComboBox_AfterUpdate()
With Me.lastnameComboBox
  Me.FirstName[blue].Caption[/blue] = .Column(1)
  Me.Profession[blue].Caption[/blue] = .Column(2)
  Me.NPI[blue].Caption[/blue] = .Column(3)
  Me.LIC[blue].Caption[/blue] = .Column(4)
  Me.ADDRESS1[blue].Caption[/blue] = .Column(5)
  Me.Address2[blue].Caption[/blue] = .Column(6)
  Me.City[blue].Caption[/blue] = .Column(7)
  Me.State[blue].Caption[/blue] = .Column(8)
  Me.Zip[blue].Caption[/blue] = .Column(9)
  Me.EmailAdd[blue].Caption[/blue] = .Column(10)
  Me.Phone[blue].Caption[/blue] = .Column(11)
End With
End Sub

If they are TextBoxes, you may want to change .Caption to .Text

And if your 'field' Me.NPI (for example) is a checkbox or an option button, you will get (probably) an error:
"Compile Error: Method or Data Member Not Found" because the default property would be a Value, not a Caption, and you cannot assign Value to an option button of "ABCD"

Have fun.

---- Andy

 

[JillianM]

Well, I am a pretty basic Access user. What happened is that one of the gals here at work was having to update 2 Excel spreadsheets and a Word document with identical data. I told her I MIGHT be able to help simplify and now I am in way over my head. And of course, no one in my whole company has ever used Access (wth). So anyway, I consolidated the two spreadsheets into one, which needs to list each doctor's information and be connected to the lab date and the cadaver type. So, the same doctor could potentially show up 50 times with different lab dates. Since she wanted to link in the spreadsheet, wouldn't I have to do it that way so that the spreadsheet would update appropriately? Spreadsheet is attached above with the db so if you want to see what i mean, you can.

 

[randy700]

I agree with Duane that you are probably going about this in the wrong manner. However, I think I know the reason you're having problems here.

The code Me.FirstName = Me.LastNameCombobox.Column(1) implies that you have a text box name FirstName. Since your original name of the combo box was combobox53, I suspect all of your text boxes are similarly named (Text54, Text55, etc).

Should you decide to continue with this method of updating your database, change the text box names to something more meaningful, just as you did with your combo box.

Suggested names: txtFirstName, cboLastName, etc

Then alter your code to reflect the correct names:
Me.txtFirstName = Me.cboLastName.Column(1)


Randy

 

[JillianM]

Any, the other boxes are fields from my Tracy_Lab spreadsheet, not labels, and all bound to the Tracy_Lab table.

 

[dhookom]

If they are TextBoxes, you may want to change .Caption to .Text

The Text property is rarely used in Access and will error out if the text box/control doesn't have the focus. Use the .Value property or no property since .Value is the default property of a bound control.

I asked a while back if you have compiled your code. Did you try this? It's in the debug menu option. The top of your code in the (General) (Declarations) section should look like:

Option Compare Database
Option Explicit

Duane
Hook'D on Access
MS Access MVP

 

[JillianM]

Hi Duane,

Yes, I did attempt to compile. I get the same error message.

 

[dhookom]

If you get an error, which line is highlighted?

Duane
Hook'D on Access
MS Access MVP

 

[JillianM]

Same as before. The very first line...

Private Sub LastNameComboBox_AfterUpdate()

 

[dhookom]

Are you sure you have a combo box named "LastNameComboBox" or is it only "LastName"?

Many of us use a naming convention that begins all combo box names with "cbo" so the name of the combo box control would be "cboLastName".

If the last name is the bound column of the combo box, you may have issues when there are multiple Drs with the same last name.

Duane
Hook'D on Access
MS Access MVP