Results 1 to 11 of 11

DLookup returning the first value of table all the time

  1. #1
    03jh01 is offline Novice
    Windows 7 Access 2003
    Join Date
    May 2010
    Posts
    4

    DLookup returning the first value of table all the time

    Hello Everybody. I am trying to use DLookup but failing. I am making a booking sytem for a theatre and have a seatID table and a Booking table. I have a dropdown menu on my booking form that has all the seats in. This form also has a "Cost" field. What I want is when a seat ID is selected from the drop down menu, the price of that seat is displayed in the "Cost" field. This value is retrieved from the Seat table that has seat ID and price in. I started out with this code:



    Private Sub SeatIDCombo_AfterUpdate()

    Cost = DLookup("Price", "tblSeat", "SeatID=" & SeatID)

    End Sub

    I was getting "the runtime error 2001" and it was telling me that I "canceled the previous operation." I fixed this with the code below:

    Private Sub SeatID_AfterUpdate()
    Price = DLookup("Price", "tblSeat", "SeatID=" & "SeatID")
    End Sub

    Now I get no errors. However, the Dlookup is now returning exactly the same value regardless of the seat ID selected (which obviously shouldn't be the case as in the seat table, the seat ID's have different prices attatched to them).

    Please Please Please help me sort this out. It's literally driving me insane

    Thanks hugely in advance

    I have now realised that the Dlookup is returning the first value in the seat table for all seatID's. Why would it be returning the first value of price for all seat ID's???

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    See if this helps:

    DLookup Usage Samples

    That said, presuming they come from the same place, I'd add the cost field to the combo row source and do this:

    Cost = Me.ComboName.Column(1)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    03jh01 is offline Novice
    Windows 7 Access 2003
    Join Date
    May 2010
    Posts
    4
    Thanks a lot for your reply. Could you explain how to use the form control statement at the link provided please? I am very new to this stuff Just the form!FormName!ControlName etc. Thanks a lot.

    Further, the price comes from the seat table and this is what I want to put in the cost field. Where would I put the Me. statement you explained and in which row source do I put the cost field. Sorry again... I am a big noob at the moment

    Thanks again

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    As you might guess, "FormName" is replaced by the name of your form, "ControlName" is replaced by the name of your control (textbox, combo, etc).

    The other theory is that when the seat ID and cost are in the same table, your combo is already doing the lookup for you. In its row source, make sure the cost field is included, then the sample code I posted would be in its after update event.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    03jh01 is offline Novice
    Windows 7 Access 2003
    Join Date
    May 2010
    Posts
    4
    so, I should put the 'control name' as combo? Edit: It doesn't seem to recognise Combo or ComboBox

    Thanks

  6. #6
    03jh01 is offline Novice
    Windows 7 Access 2003
    Join Date
    May 2010
    Posts
    4
    YESSSSS! Thank you thank you thank you. You have probably saved my life. I used the systax you provided but with SeatID in the 'control name' part and it works. Thanks so much for your help and time. Definately +++ REP !

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    Excellent, glad you got it working. Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    I have a question regarding this thread. I am trying to do something similar by having a label box populate after the update of two combo boxes by looking up the combination of the result of the two combo boxes in a table.

    I set the row source of the list box to be the table and the where condition to be the concatenated strings from the combo boxes. It appears to be working except that is will not update when the combo boxes update. I have tried the listbox.requery syntax and that does not seem to work. Any ideas?

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    Requerying the listbox should work. What exact code are you using and where do you have it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    The code used to requery is below and is located in a form module for the form that contains Cbo_Ptype1 and lstInt1%.

    Private Sub CboPtype1_AfterUpdate()
    lstInt1%.Requery
    End Sub

    The row source coding is

    SELECT [Com%_tbl].[InitialSale%] FROM [Com%_tbl] WHERE (([Forms]![Clients_frm]![CboCname1] & [Forms]![Clients_frm]![CboPtype1]=[Com%_tbl]![CompanyPolicyType]));

    Is that what you were looking for?

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    The symbols aren't a great idea. Try this:

    Me.[lstInt1%].Requery

    Or can you post the db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Query not returning all of the results
    By velvettiger in forum Queries
    Replies: 4
    Last Post: 03-11-2010, 06:56 AM
  2. Replies: 2
    Last Post: 02-14-2010, 01:38 PM
  3. How do I get dlookup values to store in table
    By rpmyhero in forum Access
    Replies: 1
    Last Post: 11-25-2009, 05:57 AM
  4. Replies: 0
    Last Post: 08-04-2009, 09:14 AM
  5. Calling a function and returning a value
    By 3dmgirl in forum Programming
    Replies: 0
    Last Post: 04-23-2007, 02:20 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
  •  
Tech Forums: Microsoft Office Forums