Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Feb 2004
    Posts
    33

    Angry Unanswered: Want to automatically fill on fields in a form based on a selection

    OK, so I am struggling with DLookUp here :/

    I have a FORM based on a table (AdmissionsInquiry)

    Have a few fields:
    ProgramID
    ProgramAddress
    ProgramPhone

    These fields also exist in another table (Programs)

    When a particular ProgramID is chosen (drop down) I want the address and phone to be automatically filled in:

    soooo
    1. I can't seem to get the syntax of the lookup correct
    2. Do I put the lookup in the ProgramID field OR in the two fields I want filled in?

  2. #2
    Join Date
    Feb 2004
    Posts
    33

    Re: Want to automatically fill on fields in a form based on a selection

    I tried this in the field I want to get filled in:

    DLookup ( [Programs]![ProgramAddress] , [Programs]= '"& [AdmissionInquiry]![ProgramID]&"'" )

    I put it in defualt value field....

    Its doesnt work though

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Want to automatically fill on fields in a form based on a selection

    Originally posted by northbrit
    I tried this in the field I want to get filled in:

    DLookup ( [Programs]![ProgramAddress] , [Programs]= '"& [AdmissionInquiry]![ProgramID]&"'" )

    I put it in defualt value field....

    Its doesnt work though
    Noooo ... You have to put it in code ... Have you written any VBA code? You need to put you DLookUp function in the AfterUpdate event of the control you want to use.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Private Sub Combo0_Change()
    call UpdateFields(Combo0.value)
    End Sub

    Private Sub UpdateFields(value As String)
    ...
    End Sub
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Posts
    33
    ok, I tried putting in After Update earlier. So I put in the field that is controlling:

    =DLookUp("[ProgramAddress]","[Programs]","[ProgramID]='" & [AdmissionsInquiry].[ProgramID] & "'")

    and it says "The object doesnt contain the automation object 'AdmissionsInquiry' "

    Strangely enough when I go back into the form design to look at the experssion it has removed a couple of square brackets and the experession is given as:

    =DLookUp("[ProgramAddress]","[Programs]","[ProgramID]='" & AdmissionsInquiry.ProgramID & "'")

  6. #6
    Join Date
    Feb 2004
    Posts
    33
    Originally posted by r123456
    Private Sub Combo0_Change()
    call UpdateFields(Combo0.value)
    End Sub

    Private Sub UpdateFields(value As String)
    ...
    End Sub


    Sorry, I am too much of a noob to really know what this means, lol. I think this solution might be over my head...

  7. #7
    Join Date
    Feb 2004
    Posts
    33
    Originally posted by northbrit
    ok, I tried putting in After Update earlier. So I put in the field that is controlling:

    =DLookUp("[ProgramAddress]","[Programs]","[ProgramID]='" & [AdmissionsInquiry].[ProgramID] & "'")

    and it says "The object doesnt contain the automation object 'AdmissionsInquiry' "

    Strangely enough when I go back into the form design to look at the experssion it has removed a couple of square brackets and the experession is given as:

    =DLookUp("[ProgramAddress]","[Programs]","[ProgramID]='" & AdmissionsInquiry.ProgramID & "'")
    anyone?

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by northbrit


    Sorry, I am too much of a noob to really know what this means, lol. I think this solution might be over my head...
    That's the after update event code. You place it with the rest of the VBA code ... How to? With the form in Design View click on View->Code and then cut and paste ...

  9. #9
    Join Date
    Feb 2004
    Posts
    33
    Originally posted by r123456
    Private Sub Combo0_Change()
    call UpdateFields(Combo0.value)
    End Sub

    Private Sub UpdateFields(value As String)
    ...
    End Sub
    Um, so I just post this as is into the code?? Sounds suspiciously easy.....

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are trying to talk to [AdmissionsInquiry].[ProgramID] but you told us that [AdmissionsInquiry] is a table.

    probably you should be trying to talk to a form or a textbox on a form that is bound to your table [AdmissionsInquiry]
    try just dropping the:
    [AdmissionsInquiry].
    ...should at least change the error message

    izy


    later (after reflection).
    that was a stupid suggestion: you end up with ProgramID = ProgramID which is a long way from being exciting.

    i think we need to know:
    tablename(fieldname1, fieldname2)
    formname.comboname formname.textboxname etc
    for everything that is involved in this process.

    izy
    Last edited by izyrider; 02-11-04 at 13:37.
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Posts
    33
    =DLookUp("[ProgramAddress]","[Programs]","[ProgramID]='" & [ProgramID] & "'")
    OK, so now I get no error message but nothing happens

    Note that ProgramAddress and ProgramID fields exist in both [Programs] and [AdmissionInquiry] tables.

    Again, what I am trying to do is have the user select a ProgramID from a drop down box (pulled from [Programs]) assign this value to [AdmissionInquiry].[ProgramID], then have the ProgramAddress on the form (and hence [AdmissionsInquiry] table) get atomatically filled in.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yeah - see my edit.
    that was one of my dumber suggestions.
    .
    .
    .
    .

    lets start with a BOUND form with .recordsource = AdmissionInquiry

    on this form you have your UNBOUND combo (let's imagine it's called myCombo) with a rowsource something like:
    SELECT programs.programID FROM programs;

    on this form you also have the textbox ProgramID BOUND to AdmissionInquiry.ProgramID

    then, in myCombo_AfterUpdate()

    Private Sub myCombo_AfterUpdate()
    ProgramID = myCombo
    End Sub

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Feb 2004
    Posts
    33

    Talking

    mmm, now I wish I had the CD on me so I could install the documenter, lol

    I guess I will try and give the fields of note:

    [AdmissionsInquiry]
    InquiryID (autonumber)
    StudentID (number)
    StudentName
    StudentAddress
    ConsultantID (text)
    ConsultantName
    ConsultantPhone
    ProgramID
    ProgramContactID
    ProgramName
    ProgramAddress

    [Programs]
    ProgramID
    ProgramName
    ProgramAddress

    [Consultants]
    ConsultantID
    ConsultantFirstName
    ConsultantLastName
    COnsultantPhone

    [Students]
    StudentID
    StudentName
    StudentAddress

    The [AdmissionInquiry] tables is related to the other 4, linked to the 4 ID fields.

    I want a form that basically is the key data entry point for admissions. I have managed to set up so it has student and admissions info on it. The part that I am stumped on is the lookups. I want to lookup and associate a consultant, program and program contact to an admissions inquiry. I have tried subforms, lookups, setvalues.....

    You will note that the Addmissions Inquiry table is duplicating some fields like ConsultantName from the Consultant table. This seems wastful to me but I dont know how to get around it.

  14. #14
    Join Date
    Feb 2004
    Posts
    33
    added db
    Attached Files Attached Files

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    we're both typing at the same time so this is getting messy.

    anyhow, back to my most recent scenario and your original question.

    modify you .rowsource for myCombo so it looks something like
    SELECT programs.programID programs.programphone, programs.programaddress FROM programs;

    set columncount = 3
    keep boundcolumn = 1
    set columnwidths to suit your preferences

    then if you have BOUND boxes on your form programphone and programaddress, your sub() reads:

    Private Sub myCombo_AfterUpdate()
    ProgramID = myCombo
    programphone = myCombo.column(1)
    programaddress = myCombo.column(2)
    End Sub


    and you wont need to mess with phone/address once you have normalised your tables. izy
    Last edited by izyrider; 02-11-04 at 14:16.
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •