Results 1 to 7 of 7
  1. #1
    Bouchardjaf is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    4

    HELP! Trying to use the look up wizard to establish relationships and getting errors

    Good Morning,



    I have a very simple database that I'm trying to set up for operators in my call center. I have one account that needs us to dispatch calls depending on the town they're calling from. If a customer is calling from Hartford, CT then the operator dispatch operator will need to call Yellow Taxi Cab Company for the customer to get them a ride. I have one table set up for the customers Information, and another table that has the City/State in one column, and the instructions for which Cab company to call in the next second column. I want to be able to select the city and have the taxi vendor information auto populate in another field.

    I've tried to set up relationships, and write the VB code, but am unsuccessful. I either get errors about indexing and duplicates? Or, I am able to write the code (me.cbo.value = etc.....) but when I close out of VB my form does not do what I intend it to do. My level with Access is basic, I can learn it and understand it, but don't know EVERYTHING there is to know about building a database.

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    If you are saving the cab company information into another table (customer), you shouldn't be - what happens if the cab contact information changes? Then all the relevant customer records need to be changed as well.

    What you really want to do, I think, is whenever a dispatch operator has information for a client on the screen, it should show the cab company information as well.

    My suggestion would be to use the DLookup function to retrieve the cab company information based on the city/state information in the current customer record, and display that information in a separate form control. One place to put that bit of VBA is in the On Current event of the customer form; the DLookup might look something like this:

    me!CabInformationControl = DLookup("CabCompany","CabInformationTable","CabCity = '" & me!City & "' AND CabState = '" & me!State & "'")

    Change the parts in italics to match your filed, table and control names.

    It looks complicated, but it isn't really - check MS Access help for information on the DLookup function.

    You would do much the same thing when new customer information is being entered, but using a different Event.

    Post back if you need more assistance.

    HTH

    John

  3. #3
    Bouchardjaf is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    4
    Honestly? I didn't know about the dlookup function. This will work. I want to select the town from a drop down combo box (which I've successfully done...) and then underneath it in a regular text box have the taxi vendor data auto populate. You're correct, I don't want to save the taxi vendor information more so have that populate in the form for the operator to easily see and call.

    My Customer table is called: ShoreLineT
    My Taxi Vendor table is called: VendorT
    My form is called: ShoreLineF

    The taxi vendor information is in my VendorT table in a column called TaxiVendorInfo, and the City/Town is in the same table (VendorT) under a column called PickupCityTown

    When I create a normal text box, and I use the dlookup function in the 'all' tab of the property sheet under control source, I am not sure of the correct formula to use. =dlookup("expression","Domain",[Criteria])

    I'm not sure which pieces of my table go into that formula....

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    The best place to do this is in the After_Update event of the combo box.

    Open the Properties dialog for the combo box, and scroll down to After Update in the list. From the dropdown list of options, select Event Procedure, then click the ... button to open the code module, which will default to the (empty) After_Update Sub for the combo box. Enter this line:

    me!CabInformationControl = DLookup("TaxiVendorInfo","VendorT","PickupCityTown = '" & me!City & "'")

    Replace CabInformationControl with the name of your text box, and City with the actual name of your combo box.

    This should work, if I have the names right.

    John

  5. #5
    Bouchardjaf is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    4
    John,

    I entered me!Taxivendorinfo = DLookup("TaxiVendorInfo","VendorT","PickupCityTown = '" & me!pickupcitytown & "'")

    I've attached what my design view looks like and maybe you can point out my mistake? I've done what I think you are asking and get an error.

    I've attached those as well. I really appreciate your help!!
    Attached Thumbnails Attached Thumbnails Design View.JPG   Error.JPG  

  6. #6
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    It looks like you may have put the code in the wrong place. If you look at the property sheet for the 'PickupCityTown' combo box control, the line for the After Update property should read "[Event Procedure]". If it doesn't, what does it read?

    John

  7. #7
    Bouchardjaf is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    4
    John,

    You were correct, I put it in the wrong place. I've corrected the issue, and the dlookup now performs exactly what I need it to do! This forum is a great place for questions, and your feedback and help has been GREAT! Thank you very much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-30-2013, 02:19 PM
  2. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  3. Replies: 13
    Last Post: 04-20-2012, 05:47 AM
  4. Replies: 10
    Last Post: 07-25-2011, 12:07 PM
  5. SQL that errors in VB
    By Wayne311 in forum Programming
    Replies: 3
    Last Post: 02-14-2011, 10:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums