DLOOK UP FUNCTION 1

[PPJOSEPH]

I have two tables - tblCourses & tblUsers:
tblCourses is only for reference purposes – to help the user to select a course.
All records are stored in tblUsers.
The fields in tblCourses are CourseTitle, CPEs, startdate, enddate, cost
The fields in tblUsers include all the fields in the tblcourses besides the employee’s name, address, city, state, title etc
The user currently has an input form frmERFTEdit that has all the fields of the tblCourses.
I want the user to have an option to pick a course in CourseTitle field, by using a drop down arrow and the corresponding fields (CPEs, startdate, enddate, cost) are automatically filled in.

What I did so far:
I ran a query “qryCourses” of tblCourses with all the required fields. It provides a drop down selection of the courses but Dlookup function for the rest of the fields is not working

I hope I made it clear enough.

Thanks, in advance for any help and/or suggestions.
Paul

 

[mdav2]

The way I would do this would be to have the main details of tblUsers displayed and have the course code change the display the course details in a subform. The subform would have a query as its source with the linking field on the course select.

You would be able to print both course and user detials in reports based on a query. (this sort of breaks the whole many to many relationship but should work because you're only using the table as a lookup and the struture is simple).

If you do try and insert the details into the user table then if you make any changes to the courses details they won't link back to the original users data.

Hope this helps.

 

[PHV]

Take a look at the Column property of the ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

You have many options like below
Open RecordSet with DAO

Private Sub cboLastName1_AfterUpdate()
On Error Resume Next
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String    
    
    cboLastName1.SetFocus
    If cboLastName1.Value > 0 Then
        strSQL = "SELECT * FROM tblTable WHERE ID = " & cboLastName1.Value
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
        If Not rs.BOF Then
            Me.ID = rs("ID")
            Me.Prefix = rs("Prefix")
            Me.FirstName = rs("FirstName")
           'REST OF THE FIELDS
        End If
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
    End If
    
End Sub

DLookUp

 Me.ID = DLookup("[ID]", "tblTable", "[ID]=" & cboLastName1.Value)
        Me.Prefix = DLookup("[Prefix]", "tblTable", "[ID]=" & cboLastName1.Value)
        Me.FirstName = DLookup("[FirstName]", "tblTable", "[ID]=" & cboLastName1.Value)

Column Property as PHV stated

Me.ID = cboLastName1.Column(3)
    Me.Prefix = cboLastName1.Column(4)
    Me.FirstName = cboLastName1.Column(1)

You can use ADO also to the same..
hope this helps

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[PPJOSEPH]

Thank you Zameer. Can't ask for more. You are awesome. Here is a star you deserve it!! Not only you answered my question but also gave me options.
Thanks PHV for replying but did not know where to start.
Zameer: Final question I want to narrow the user's selection in the drop down arrow by using the startdate criteria - something like >=Date().
Is it possible to insert the criteria within your sqlstmt. I can do it by appending a query to a table with the above criteria but someone has to run the query all the time.
Again thanks and it works great.
Paul

 

[perrymans]

Also, fix your database structure.

tblCourses-holds course info
tblUsers-hold user info
tblUserCourses-here is where you make the link between the two with only two fields: CourseID and UserID. The info for each would be pulled from their respective tables onto the form.

Sean.

 

[ZmrAbdulla]

PPJOSEPH,
If you have a CourseDate Field in your Courses tbale then you can add the >=Date()to the combobox Rowsource.
Someting like..
[tt]
SELECT tblCOURSES.CourseTitle, tblCOURSES.Date FROM tblCOURSES WHERE (((tblCOURSES.Date)>=Date())) ORDER BY tblCOURSES.CourseTitle;
[/tt]
Also think about changing the setup as perrymans & mdav suggested..
hope this helps

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[PPJOSEPH]

Thanks Zameer. Your codes was of great help.