Forms Relationship

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

OK this is really simple - but I just can't get it to work.

I have 2 tables: "Customers" and "Pictures" both with
customer ID fields to link them.

I have created a unbound list box to list the names of the
pictures on a form, which then displays the selected
picture. (Me.CardImage.Picture = Me.Cardlist.Column(1))

I only want the list box to show the names of pictures
which are relavent to the customer currently showing on
the form, and to automatically update if i select the next
customer.

I'm no guru, but this should be so simple, I just can't
make it work :o(

Please help, anyone!
Regards - Gary
 
You haven't said what you've tried, but you need a query in the RowSource of
your listbox whose WHERE clause is:
WHERE CustomerID = forms!FormName!CustomerID
(replace your actual form name for FormName)

Now, when you switch records, because the listbox is unbound, it will not
update the listbox contents. Therefore you need to requery the listbox when
you change records. You do this in the OnCurrent event of the form. Do
something like this:
Me.MyListbox.Requery
(once again, replacing your actual listbox name for MyListBox)
 
Thank you Roger that worked - I was typing the WHERE part
incorrectly!
One more question:
I want to be able to add 2 more text boxes bound to
the "pictures" table (to show the name and path of the
picture) but the fields are not listed in the Control
Source? How do I do this?
Many Thanks - Gary
 
One other thing (sorry to be a pain)
Is it possible to revert the image to the original image
when I change from customer to customer

(Me.CardImage.Picture = Me.Cardlist.Column(1))
is what I use to update the image.

Your help is greatly appreciated!!!
Many thanks - Gary
 
What do you mean by the 'original picture'? The first picture in the list?
If so, put this in the OnCurrent event:
Me.MyListBox.Selected(0) = True
 
The fields in the "Picture lookup Cards" Table
are "CustomerID", "PictureID" and "Path"

The "PictureID" list box is working perfectly no - thank
you! and I just need to show the other 2 on the same form
showing the correct data for the picture that is currently
being displayed. (the pictureID names are CD001 CD002
CD003.... for each customer) so there are many duplicates.

Thanks - Gary
 
So in the PictureID listbox, you have a RowSource with CustomerID and
PictureID? Or do you also include Path? If so, and if your listbox has 3
columns in the columns property, on the AfterUpdate event of the Listbox,
you can assign the value of the third column to another textbox. Like so:

Me.txtPath = me.PictureID.column(2)

Now, if you want the path in one text box and the file name in another, you
will have to split it. On my website, see sig below, is a small sample
database called GetPathFileName.mdb. Copy the module into your application
and you can do this:
Me.txtPath = GetPath(me.pictureid.column(2))
Me.txtFile = GetFile(me.pictureid.column(2))
 
Does the "Me.MyListBox.Selected(0) = True" get listed like
this:

Private Sub Form_Current()
Me.Cardlist.Requery
Me.Cardlist.Selected(0) = True
End Sub

Sorry to be a pain, but Im pretty new to access (as if you
need telling!)

Cheers Gary
 
Back
Top