List Box in a Form

[Harry Taylor]

Please help.

I have a table of customers, addresses etc.
I also have a table to correspondence, who I called and when.

My customers are displayed in a form view and i want a list box in this form to show the call history (from the other table).

The trouble is that the form view shows every records call history, not he one I am looking at. I have tried running a query from the Row source but have'nt get anywhere. :banghead:

Please Help

Just to add to the probem, I have no experiance with VBA

Thanks

 

[MikeLeBen]

I'm assuming on your call history table you have a fk linked to customerID in your customers table (I shouldn't be assuming as it takes longer to identify the correct solution for you, and therefore wastes more of both your and my time - please read the guidelines).

Under that assumption, what are you using as Row Source for your listbox?

 

[Harry Taylor]

Hi Mike,

My Customer address table uses the customer account numbers (1 to 3000) as the primary key (no duplicates).

The call history shows the account number but does have duplicates (a new line each time I call them).

I have set a relationship between both fields and when I expand the view in the customer address table it shows me exactly what I want to see.

In the Row Source I am looking at the Call History table. It currently shows me the call history of every account and I only want to see the call history of the account im viewing on the form.


Thanks

 

[boblarson]

So why are you using a listbox instead of a text box for this?

 

[MikeLeBen]

Use a select query in your listbox to filter based on whatever record you're viewing.

On the Load event of your form put something along the lines of:

Dim mySQL as String

mySQL = "SELECT callday, calltime, etc FROM tblCalls WHERE fkIDCustomer = "

Me.yourlistboxname.rowsource = mySQL & Me.IDCustomer

 

[boblarson]

Use a select query in your listbox to filter based on whatever record you're viewing.

On the Load event of your form put something along the lines of:

Dim mySQL as String
 
mySQL = "SELECT callday, calltime, etc FROM tblCalls WHERE fkIDCustomer = "
 
Me.yourlistboxname.rowsource = mySQL & Me.IDCustomer

If using that method then you would also need to run the same code on the form's On Current event so it would change as you move from record to record. But I'm still wondering why the OP is using a listbox for this when a text box, or subform with a text box would be more appropriate.

 

[MikeLeBen]

I reckon a listbox or a datasheet subform would do well as there are going to be several calls for each ID on the main form.
Or maybe I got this wrong. Let's just wait for the OP to clarify.

 

[vbaInet]

I think the OP is trying to filter the form based on the selection in the listbox.

 

[Harry Taylor]

Gents,
Thanks for your help thus far.

MikeLeBen is correct, there are several calls for each ID on the main form.
A text box will only show one call.

I can use the Subform which does what I need but the list box looks so much better.

boblarson, I think you get what i am trying to do but i'm a bit lost with coding (Can you hold my hand )
I have run a query using the event builder, but cant see how to make a filter/query to show the record i'm viewing on the main form.
(hope this makes sense)

 

[MikeLeBen]

Harry,

you can use the method I stated in an earlier post, and like Boblarson said you'd need to also use that code in the On Current event of the main form.

 

[Harry Taylor]

MikeLeBen,

Thanks for your help.

At the risk of becoming a pain, can you spell it out to me (property sheet, select Data tab etc).
I have no experiance of VBA and have only been working with access for a short time.

Your help is very much appreciated and we were all noobs at some point

 

[MikeLeBen]

Open your form in design view, in the property pane located to the right (if it's not there you can find the button to open it in the tools ribbon on top) select the "Events" tab, and find On Load -> click it -> you'll be brought to the VBA console.
In it paste the code I gave you, although you'll probably need to change the SQL query.

Then close or minimize the console, go back to access and repeat this step with the On Current event.

I'm still a noob, but I hope I can help regardless of it

 

[Harry Taylor]

Hi Mike,

I cant find "On Load", I have: On Click, Before Update, After Update?

Is it one of these?

 

[MikeLeBen]

Have you selected "Form" from the combobox on top that lets you select the object?

 

[boblarson]

Hi Mike,

I cant find "On Load", I have: On Click, Before Update, After Update?

Is it one of these?

You are probably selecting the SUBFORM CONTROL (the control which displays/houses the subform). To select the subform itself, from the main form, click on the gray square in the top left corner of the spreadsheet.