Change Form Recordset with code.

[aunixguru]

I have a form that is based on a table (projects) that i would like to find a particular project.

I have been using the apply filter and don't like it. The reason is that I have subforms on this form that have filters as well.
When i do a docmd.showallrecords in code. All forms currently open are affected.

I would like to change the recordset to the project selected as the result of a dropdown.

I do not know how to do this in code. I think it would be done this way:

afterupdate of the combobox executes something like

recordset now = select * from projects where projectnum = comboboxproject.

apply recordset to the form's row source?

Any ideas?


Thanks,

 

[TTThio]

You're quite on the right track I guess

It's:

cbo_AfterUpdate()

dim db as database
dim rs as recordset
dim strSQL as string

strSQL = "Select * from table/query WHERE [field]= Forms![FormName]![cbo]"

set db = currentdb
set rs = db.openRecordset (strSQL)
'here then you can check if the connection works by displaying msgbox of rs.recordcount for instance

subform1.recordsource = strsql

set rs = Nothing
End Sub

Let's see if it works.
For more info go to:

http://msdn.microsoft.com/library/devprods/vs6/vbasic/dao360/damthopenrecordset.htm

Tin Tin

 

[jflachman]

You're very close to what you need.
First, the docmd is really just a way to run a macro, which is much less desirable than doing it with true VB code.

There are several ways you can solve your problem:
1) In the afterupdate of the combo box, put the following code:
[tt]
Me.RecordSource = SELECT * ... just as you have it[/tt]

2) You can set the form Filter and FilterOn properties.
[tt]
Me.Filter = "[Projectnum] = " + Me![ComboBoxName]
Me.FilterOn = True[/tt]

To turn the filter off and view all of the records:
[tt] Me.FilterOn = False[/tt]

Let me know if that's not helpful.


Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt