Look up - on a query

  • Thread starter Thread starter Fekla
  • Start date Start date
F

Fekla

Hello.

I am a newbie in Access. I am making a Boostore database. Here is the
relationship in problem:
http://moryasha.narod.ru/acc.gif

I set the Data Type for BookISBN field in my OrderDetails table to a
LookupWizard. However, I dont want it to look up all the ISBN's in the Books
table, but only those whose Publisher is the same as the publisher of the
relative order.

unfortunately, I am very puzzled and haven't been able to do it by myself.
Can anyone please kindly explain me as detailed as possible? Grateful in
advance.
 
Fekla said:
Hello.

I am a newbie in Access. I am making a Boostore database. Here is the
relationship in problem:
http://moryasha.narod.ru/acc.gif

I set the Data Type for BookISBN field in my OrderDetails table to a
LookupWizard. However, I dont want it to look up all the ISBN's in
the Books table, but only those whose Publisher is the same as the
publisher of the relative order.

unfortunately, I am very puzzled and haven't been able to do it by
myself. Can anyone please kindly explain me as detailed as possible?
Grateful in advance.
Click to expand...


You cannot (and should not) do this at the table level. Use a form with
ComboBoxes to make your ISBN entry and then you can easily make that ComboBox
behave the way you want. Look in help or search these groups for "Cascading
ComboBoxes".
 
Thank you, Sir.
I worked on the problem all day and it got a bit better. I started using
cascading combo boxes as you said and they DO look up the necessary ISBN's...
but only for the first order opened!

I guess the problem is that i dont put the ComboBox.Requery command in the
correct place.I put it on the getFocus event of the 2nd ComboBox [My idea is
that as soon as you click the 2nd Combo, it checks the value of the First]
but it does not work somehow... perhaps I got confused with the logic. Please,
do you think you can help me?

In case my explanations are not very clear, I attach the DB link
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=16005&d=1168200772
 
Fekla said:
Thank you, Sir.
I worked on the problem all day and it got a bit better. I started
using cascading combo boxes as you said and they DO look up the
necessary ISBN's... but only for the first order opened!

I guess the problem is that i dont put the ComboBox.Requery command
in the correct place.I put it on the getFocus event of the 2nd
ComboBox [My idea is that as soon as you click the 2nd Combo, it
checks the value of the First] but it does not work somehow...
perhaps I got confused with the logic. Please, do you think you can
help me?

In case my explanations are not very clear, I attach the DB link
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=16005&d=1168200772
Click to expand...

Use AfterUpdate rather than GotFocus. GotFocus is before you have made a
selection and is therefore too soon.
 
This was the first thing I tried, and it does not work.
May be because I do not always change the value of the ComboBox1, but rather,
move to the next record with a different value..
 
Fekla said:
This was the first thing I tried, and it does not work.
May be because I do not always change the value of the ComboBox1, but
rather, move to the next record with a different value..
Click to expand...

You also need to Requery in the Current event of the form. It can also work to
Requery in the GotFocus of the ComboBox that you are requerying.
 
Fekla said:
I am afraid neither of this works :(
Click to expand...

It it works in the AfterUpdate of the first ComboBox then the same line of code
should work in any other event you execute it in.

What does or does not happen?
 
The problem persists:
The second Combo Box continues to offer the values based on the value of the
first Combo Box when I open the form (including the subform).
Let me explain it in other words.

If the Publisher of Order 1 (Record 1) is Macmillan, the combobox2 in the
subform shall offer only books of macmillan for all the records.
If I change the publisher of order 1, close the form, and open it again-
combobox2 shall offer books of the chosen publisher, but again for all
records, no matter whether you navigate through records, change the publisher
etc.
 
Fekla said:
The problem persists:
The second Combo Box continues to offer the values based on the value
of the first Combo Box when I open the form (including the subform).
Let me explain it in other words.

If the Publisher of Order 1 (Record 1) is Macmillan, the combobox2 in
the subform shall offer only books of macmillan for all the records.
If I change the publisher of order 1, close the form, and open it
again- combobox2 shall offer books of the chosen publisher, but again
for all records, no matter whether you navigate through records,
change the publisher etc.
Click to expand...

Then you are NOT successfully Requerying combobox2. What exactly is the code
you are using and where exactly are you calling it?
 
okay, not without outside help, i found a solution:

Private Sub Form_Current()
Me.frmSyb.Form![BookISBN].Requery
End Sub

it changes offered ISBNs both if you move to different record
or if you change the publisher of the selected record

(i m just posting this in case someone will need this thread for future
reference)
anyone, grand merci for the assistance.
 
Back
Top