Frustration with DLookup 1

[xqzme]

I am new to VB coding and am having two problems with DLookup function which I am unable to solve.

I have two tables, Patient and Surgeons, linked by SurgeonID. On a Patients form I have a combo box which populates the surgeons last name. I am trying to populate text boxes for a surgeon identifier called SurgeonULI (long integer) and an additional text field in my Patients form from the Surgeons table.

Part of the code being used is as follows:

TempULI = DLookup("[SurgeonULI]", "Surgeons", "[SurgeonLastName}='" & [Forms]![Patient]![SurgeonLastName] & "'&quot
Me!SurgeonULI.SetFocus
SurgeonULI = TempULI

On execution I receive a null error even though the SurgeonLastName is entered approrpiately. Why is this??

Second issue. When I execute this sub, a new entry appears in my Patient table. It enters the appropriate SurgeonID as the SurgeonLastName. How do I stop this from happening?

Thanks in advance

 

[JeremyNYC]

There's a problem more fundamental than your code. You should do this by linking the surgeon ID, not the surgeon name. If this is a patient form, you should pick the surgeon out of a combo box or list box that contains all the possible surgeons.

You say it fails even if the name is right. OK, that's fixable. But what if the name is typed wrong? Cases like this cry out for a combo/list box.

If you need to display more than just the surgeon's name, you will probably want to use a subform. Or, if there's only ever one surgeon per patient, just base the form on a query that combines the two tables--the other fields will be updated when you update the combo box. If you use this method you'll proabably want to lock/disable the surgeon fields, as you don't want to make changes to those on this form.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Independent Access consultant.

If a post is helpful, give it a star.

 

[PaulBricker]

You don't need a dlookup for this. Using the column property should do it. The second problem is probably due to the wrong Bound Column in your Combo. Here's what you do.

1. Open the Form in Design view and open the properties box. Select the combo box and look for the RowSource Type. Click on that row and then select the elipsis. The ... three dot box. This should open a query that contains your combo information.
2. The query should have your SurgeonLastName, SurgeonULI, and the other field you want to fill your textbox on the form.
3. Make a note of what order the Fields are in, in the Query.
4. Go back to the Properties box for the Combo. The bound column for the Combo box should be the same as your SurgeonLastName. So if SurgeonLastName was the second column in your query, the Bound Column for the Combo should be 1. That right, 1. Combo box columns are zero based so the first column in 0, the second is 1 and so on.
5. Now to fill in the other two values, put this expression in the control source for each textbox. Change the column value for the correct column
SurgeonULI will be
=ComboBoxName.Column(0)
For the other textbox
=ComboBoxName.Column(2)

That should fill those values in after you select a SurgeonLastName.

Paul

 

[PaulBricker]

Edit to previous post.
1. Open the Form in Design view and open the properties box. Select the combo box and look for the RowSource. Click on that row and then select the elipsis. The ... three dot box. This should open a query that contains your combo information.

Paul

 

[xqzme]

Thanks Paul. This is indeed the solution I was looking for on the first matter. I continue to suffer from the latter problem.

The form data now inputs appropriately. As I make my entries on the form, the Surgeon table is also updated with a new sequential SurgeonID and a numeric entry (e.g. the numeral 6)in the SurgeonLastName field in a new line beneath my table entries. The remainder of the table fields remain null.

I cannot clearly identify the entry as being related to any one thing and it is certainly not related to the associated SurgeonID from the Surgeon table. Any thoughts on what might be going on?

 

[PaulBricker]

I'm just a little confused by your second paragraph. How is the Surgeon table updated if this is done on a Form tied to Patient. Does your Surgeon Table have a one to many relationship with Patient on SurgeonID, Surgeon Table being the one side and Patient Table being the many.

Paul

 

[xqzme]

Patient Table includes PatientID, PatientLastName,PatientFirstName,SurgeonID.

SurgeonTable includes SurgeonID, SurgeonLastName, SurgeonFirstName, Address, SurgeonULI. (My model for this approach was based upon the Northwinds example included in Access 2002)

There is a one-to-many relationship between surgeon to patient i.e. one surgeon operates on many patients.

For the sake of working through this glitch I have entered only 6 surgeons into my Surgeons table. Assume I am about to make my first entry into the Patient form.

I key in the patient surname and the given name and then come to the SurgeonLastName combo box. This combo box now works properly on the form and fills in the SurgeonFirstName and the Surgon ULI.

If I then open my Patient table to review my entry, I have a numeric entry in the SurgeonID column e.g. "3". If I open the Surgeon table, a new entry appears in the 7th row of my Surgeon table with the numeral "3" appearing in the SurgeonLastName column. I thought the "3" might be related to the ID number of my designated surgeon but this is not the case i.e. the Surgeon entry is in the 2nd row of my table but the number appearing beneath SurgeonID in the 7th row is a "3".

Ultimately I would like to display the SurgeonLastName rather than the SurgeonID on my Patient table (as is done in the Northwinds example). I have yet to figure out how that is done as I am also fairly new to Access.

Apologies for the lengthy description. I am hoping that by being specific I may offer you a clue in identifying the problem. This is but a small part of a much larger RDB I am trying to develop. This problem has been particularly frustrating.

Trust this this clarifies the problem. Thanks in advance for your consideration.

 

[PaulBricker]

xqzme, I apologize, I lost track of this post. Are you still working on this problem?

Paul

 

[xqzme]

I have yet to sort this one out. I, too, have been tied up with other matters the last several days.

I was looking at a related database belonging to another inidividual and I noted that this same problem arose with this individual's database system. Any guidance would be appreciated.

 

[PaulBricker]

xqzme, I don't think the Surgeon table should be updating with any value from the form. Do you agree with this. What is the SQL behind the Surgeon Combo box. I think you may need to change the RowSource for that box.

Paul