changing form recordsource refresh

[bobmac-]

Hi,

I'm changing the recordsource of a form using an SQL string to filter the records.

The correct number of records appear but a bound combobox doesn't display the value for the first record in the newly filtered set of records.

I've tried using me.refresh after the recordsource change and a requery on the combobox on the 'on current' event but nothing works.

Any ideas

 

[MStef]

Try with REQUERY after the recordsource change.

 

[Psycence]

Do You mean that you want to have the combo box display the first returned result rather than a blank?

in that case add in the statement

Me.[Your Control].Requery
Me.[Your Control] = Me.[Your Control].ItemData(0)

after the SQL statement has reset your form recordsource

 

[bobmac-]

Thanks for the reply

This is the line that is executed after the recordsource change;

Me.[cboCustomer] = Me.[cboCustomer].ItemData(0)

I get an error message;

'You can't assign a value to this object'

Interestingly I have another command button to 'clear' the filter comboboxes and reset the original recordsource. In effect removing the filter. This, for whatever reason, does display the combobox value that is missing when I try to filter via recordsource change.

I think that it may not be displaying because the first record in the filtered set of records may not have an 'active' combobox display i.e. a value has never (in current session) been selected from the drop down list. I'm going to try setting a default value, I'll let you know how I go.

Cheers

 

[MStef]

Send a short example of your mdb, (access 2000, 2002-2003).

 

[Psycence]

Thanks for the reply

This is the line that is executed after the recordsource change;

Me.[cboCustomer] = Me.[cboCustomer].ItemData(0)

I get an error message;

'You can't assign a value to this object'

Remove the [] I only added those to indicate where your combo box details needed to go

 

[bobmac-]

Hi Guys,

Apologies, the value I wish to display is the cust_name from the tblCustomer table which is linked to the stored cust_id value in the current record.

I've attached a stripped down version of my database.
The customer name combobox 'cboCustomer', has two fields, cust_id and cust_name from the customer table (tblCustomer). The first field is bound to the cust_id field in the current record of the (tblProposal_Application) table.

What I wished happens is as follows;
On selecting another name from the proposal filter customer combobox (cboCustomerSearch) and clicking the GO button, the recordsource is reset.
The customer name control is then displays the cust_name from the customer table using the current records stored cust_id

I hope its a little clearer
Once again sorry for the confusion

Cheers

 

[bobmac-]

Hi,

I'm to mess around with the recordsource string.

The CLEAR button on the form works perfectly using
strFilter = "SELECT * FROM tblProposal_Application" as the input to the recordsource

The GO button (Filter) does the same thing if I take out the WHERE clause. When I add the WHERE clause in I get a blank display

The string with the WHERE clause is below

strFilter = "SELECT * FROM tblProposal_Application, tblCustomer WHERE tblProposal_Application.cust_id = tblCustomer.cust_id"


cheers

 

[Psycence]

Hi Bob,

I took your database and reworked it to do what I think you wanted, now when you change the Short name near the top the records below will automatically change to the correct name records for the application table.

I set it to continuous forms so you could see it is pulling all the records, see attached

 

[bobmac-]

Thanks Psycence
The solution works great. Unfortunately it only returns proposal_application records for one cust_id. You weren't to know, so I apologise for that, but it is possible for the one short name to have multiple cust_id's.

One of the IT people hit on the reason the original SQL wasn't working before.

Previously the two tables (tblCustomer, tblProposal_Application) both had a cust_id field. This caused Access to append the table name to the front of one of the tables cust_id. As the cust_id was bound to the tables cust_id the query couldn't find it.
The solution; change the tblProposal_Application cust_id tp papp_cust_id
i.e. We no longer have two tables with the same cust_id field
The final SQL is as follows;
strFilter = "SELECT * FROM tblProposal_Application INNER JOIN tblCustomer ON tblProposal_Application.papp_cust_id = tblCustomer.cust_id " & _
"WHERE tblCustomer.cust_short_name = " & "'" & Me![cboCustomerSearch].Column(1) & "'"

Once again many thanks and I hope what I've done above makes sense