settings for a combo box on a form - bound or unbound?

[merlin777]

I have a form showing fields from my main table. I have a subform on the form showing a query with a filtered list of records from my main form. I want the query parameters to be selected from the form, a date with a date picker on a text box (got that covered) and a name chosen in a combo box that gets it's list from a second table called tblnames. tblnames is related to the 'names' field in the main table.

I want to point the combo box at the tblnames table and show the two fields 'first name' and 'lastname' so I can select them and pass them to the query as a parameter for the filter.

I can't work out how and I'm not even sure if it something should be bound to something else or not.



Please could someone assist?

[June7]

Controls used to enter filter criteria should be UNBOUND.

If you want to use dynamic parameterized query, review http://www.datapigtechnologies.com/f...tomfilter.html
That example works only for text type fields. Number and date/time types have to be handled differently because wildcard does not work with them.

I don't use dynamic parameterized queries. Review http://allenbrowne.com/ser-62code.html

[merlin777]

Thanks for this. I used the first example because I'm not up to VBA level yet.

In the example he selects combo box/properties/data/row source and clicks on the ... and then a show table list box comes up containing a list of tables to choose from. I on't get one of these - just a message asking if I want to build a query based on the table associated with my form. If I click yes I get a list of fields from this table but not the one I want to point my combo box at. If I click on no it just closes and does nothing.

The problem I keep coming back to is not being able to specify a list from a field in another table.

[June7]

I don't use the wizard. I disable the design wizards.

After the wizard closes, go set the combobox properties manually.

The videos are old (Access 2003) so the screens will look different but the principles are the same.

[merlin777]

sorry - hit send before I was finished...

he's demonstrating a combo box referring to a query with a filter. I already have my query built and I have 2 parameters to pass to it from inputs on my form - one is the date and the other is this list of names from a table that I can't get the combo box to look at.

[merlin777]

keep crossing over with you!

I've been trying to set the properties manually and that's where I'm having the problem. Which properties do I set for what?

I've had all sorts of results - I've caused the box to be bound, i've had a list of numbers, I've had a list of names which it wouldn't allow me to select. I must be missing something basic!

[orange]

You may find some help at Martin Green's site
Parameter queries

[June7]

Set combobox properties:

RowSource
ColumnCount
ColumnWidths
BoundColumn

If you want to provide db for analysis, follow instructions at bottom of my post.

[merlin777]

Thanks - db attached.

the form 'tblappointment' is the one i'm trying to get working. I think the answer might be what goes in the row source field but I can't get it to offer the fields in the table 'tbltherapists' to choose from.

The other thing I can't get working is displaying my query3 in the subform - it's working ok as a query on it's own (when I pass it the name parameter) but not in the sub form. (My query has 2 fiters and a time calculation).

Eventually I'd like to filter on date and firstname+lastname but that's a bit advanced for me at the moment. I had considered creating a field on my main table of firstname+lastname from the tbltherapist but I'm nit sure if that's good practise and I'd really like to learn the correct way rather than do a workaround.

[June7]

Controls used to input filter criteria must be UNBOUND, otherwise you change data in record.

The combobox works for me. Didn't do anything.

The main and sub forms are bound to the same data. This is not good. Why not just one form? Put search controls and buttons in the form Header. Data controls in Detail section.

You are using lookups in table. Advise not to do that http://access.mvps.org/access/lookupfields.htm

The query doesn't work because the parameters reference the wrong controls on the main form.

Remove the Master/Child links from the subform container. Then need code to Requery the subform (click the Refresh All on the ribbon for the equivalent).

[merlin777]

wow - i'll take those one stage at a time...

The main and sub forms are bound to the same data. This is not good. Why not just one form? Put search controls and buttons in the form Header. Data controls in Detail section.

if i don't use a sub form, how do i display the filtered record list from my query? How would i unbind the main and sub forms from the same data - one is a filtered version of the other?

[June7]

Not sure I understand the question. You have the subform bound to the parameterized query. It is the filtered record list. The main form is bound to the table but does not show the records.