Dlookup in VBA

[Annnnn]

Hi guys,

I'm kind of stuck with using this Dlookup function in my Form.

Within a form I want to show data from a table based upon a dropdown menu within the form.
I am using this in vba:

Private sub Hospital_click()

Patient_name = DLookup("Hospital_code", "tblHospital", "Hospital_name = '" & Forms!AddBlood!Hospital & "'")

End sub

What I want is to lookup the value from the Hopsital field within the form AddBlood into the table tblHospital and then show the Hospital_code in field Patient_name (this is also in the form).

But when using this code, nothing happens..

Hope you can help!

[Micron]

You assign the value being returned to the variable Patient_name, but that's all you're doing. I don't understand this

Hospital_code in field Patient_name

You want to put the hospital name in the control that's meant for the patient name?
If you can figure it out, you need to make the applicable control to be the result of the lookup, but I can't figure out what that is, and it may not work if that field is bound. Try editing it manually first. It may be as simple as Me.yourControlName = DLookup....
Is there a possibility that the lookup returns no value? Then you need to handle Null as well.

[ssanfu]

Kinda hard to understand what you are trying to do.

Why are you putting a "Hospital_code" in a control for Patient_name?? (I'm guessing that "Patient_name" is a control on the form?)

What type of control is "Hospital"??
Why are you using the click event?


Here is an alternative to the DLookup function:

Private Sub Hospital_click()
    Dim r As DAO.Recordset
    Dim sSQL As String

    sSQL = "SELECT Hospital_code FROM tblHospital WHERE Hospital_name = '" & Forms!AddBlood!Hospital & "'"
    Set r = CurrentDb.OpenRecordset(sSQL)
    If r.BOF And r.EOF Then
        'no record found
        Me.Patient_name = "Unknown" '<<-- for testing purposes
    Else
        'at least 1 record found.
        Me.Patient_name = r("Hospital_code")
    End If

    r.Close
    Set r = Nothing
End Sub

[June7]

Also confused. Why would a hospital code go in patient name field?

If Hospital is multi-column combobox then the DLookup() and recordset code would not be needed. Just reference combobox column that has the hospital code.

[orange]

Annnnn,

We need to clarify what you are trying to do. Better to tell readers in plain, simple English. Forget the form
and vba for the moment. Once readers understand, then you may get focused responses as to how it may be done with Access.

Good luck.

[Annnnn]

Annnnn,

We need to clarify what you are trying to do. Better to tell readers in plain, simple English. Forget the form
and vba for the moment. Once readers understand, then you may get focused responses as to how it may be done with Access.

Good luck.

Hi!

Sorry if I was a bit confusing.
First, I want to create the patient name from the hospital code because we don’t use actual names in our system but an anonymous code for every patient.
So, if I have a patient from hospital A the patient name always starts with A-02-xxxx the x = unique per patient.
When I am adding a patient into my table using a form I would like the start of the patient name to be generated so that when entering I cannot make a typo within this first part of the patient name. The thing is we have around 40 different hospitals with 40 different identifiers so this data is stored at another table.

So I enter in the field Hospital : A
Then access shows me in the field Patient_name: A-02
And I can complete the Patient_name with the correct 4 numbers to complete the Patient_name.

Is it more clear?

but maybe the code ssanfu showed is more usefull than the DLookUp?

Today I am not capable of working on it, but tomorrow I am. So if you have any idea I’m happy to hear it!

[Micron]

The "into" part of this doesn't make sense to me.

to lookup the value from the Hopsital field within the form AddBlood into the table tblHospital

Your lookup should find the hospital code from the table if the hospital name contained in the control is found in the prescribed field. But from what are you getting the form value to use as lookup criteria?

I think you need to post some sample data and the desired result. It seems like the Hospital control is a combo box, but is it a single column one? Also, the click event is wrong for this, as previously noted but I think not commented on by you. You should use After Update. Again, you don't seem to be concerned with the possibility that the function doesn't find a value. Unless you have a very large set of records, to me, a lot of code isn't necessary if a simple built in function will do.

I would like the start of the patient name to be generated so that when entering I cannot make a typo within this first part of the patient name

The value returned should go into a disabled or locked text box, and you enter the desired suffix, otherwise there's not much point in this exercise if you're going to enter the field with the returned value and possibly mess it up anyway.