#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    5
    Rep Power
    0

    Database Form Problem


    I am trying to create a form that has a predictive field that Automatically predicts and completes the name that I am typing, and when I press tab the remaining Address and Postcode fields are automatically completed. All of this information is taken from one table as a source and is to be compiled into another separate table.

    The problem I am having is that I can create a predictive field that completes what I type, I can also create VBA that will auto fill the remaining address and postcode fields automatically but I can not make both of these things work together. It seems I must have one or the other. (I must completely type the whole name and press tab to have the Address field and postcode field fill automatically or have the predictive field complete the name I type and enter the Address and Postcode Manually.

    My question is
    Why will they not work together?

    Is there a way around this Problem?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Dublin
    Posts
    413
    Rep Power
    14
    i don't understand where your problem is - i would expect what you describe to work but can't really help without some sample code
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    5
    Rep Power
    0

    The Code


    Hello EPL

    The only way I know to make a field predictive of what I type is by using a Lookup (this is where I think my problem lies.)

    This is the code to allow the Auto Fill of Start Location And Start Postcode but this code will not work if the Startpoint was chosen from a Lookup.

    Private Sub StartPoint_Exit(Cancel As Integer)
    Dim varStartLocation, StartPC As Variant
    varStartLocation = DLookup("ADDRESS", "tblPointsInfo", "NAME = [StartPoint]")
    varStartPC = DLookup("POSTCODE", "tblPointsInfo", "NAME = [StartPoint]")
    If (Not IsNull(varStartLocation)) Then Me![StartLocation] = varStartLocation
    If (Not IsNull(varStartPC)) Then Me![StartPC] = varStartPC
    End Sub

    I have a table called “tblPointsInfo” this is where the information is being referenced from, I also have a table called “Questions” this is where the information being entered into the form is going.

    I hope this elaborates a bit more and thank you for your time and effort.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Dublin
    Posts
    413
    Rep Power
    14
    the easiest / quickest thing to do is to modify your dlookup criteria parameters as follows:
    Code:
    varStartLocation = DLookup("ADDRESS", "tblPointsInfo", "NAME = """ & strName & """"")
    varStartPC = DLookup("POSTCODE", "tblPointsInfo", "NAME = """ & strName & """"")
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    5
    Rep Power
    0

    Many Thanks!


    Many Thanks EPL

    I managed to locate the problem as being something to do with using a Lookup and not a Combo Box - Since using a combo box in conjuction with the Code everything is working great.

    Thank you very much for your time.

    Regards Dean

IMN logo majestic logo threadwatch logo seochat tools logo