updating the datasheet section of a split form dynamically

[jerryczarnowski]

I have a split form that is loaded with the record source being a SQL statement and would like to update the datasheet section when the user selects from a combo box utilizing the BeforeUpdate or AfterUpdate event. I would like to apply a new SQL statement (in the event code) referencing the current datasheet and requery. Is the records displayed when the form is loaded referred to as a result set? and how do I perform a SQL statement on that result set?

Thanks, Jerry

 

[Pat Hartman]

You can replace the form's RecordSource with a different SQL string. The new string will not be saved when you close the form. It will be discarded.

Me.RecordSource = "Select ....."

 

[jerryczarnowski]

Hi Pat,

Thanks for the quick response. Usually in a select statement the FROM references a table...but in this case when I am not referring to a table or query, can I skip the FROM statement? The idea is for the user to select a Holder Company and LYNDEX is a value in the list...then the result set should show only records with LYNDEX in the Holder Company field. What should my sql statement look like? Below is the sql statement that does not work.

Private Sub cmbHolderCompany_AfterUpdate()

If cmbHolderCompany.Value = "LYNDEX" Then
Me.RecordSource = "SELECT * WHERE [Holder Company] = "LYNDEX"
Else
MsgBox ("Try again")
End If

End Sub


Thanks, Jerry

 

[Pat Hartman]

The FROM clause can reference a table or a query but it cannot be omitted. How would Jet know where to find the data you are looking for?

 

[jerryczarnowski]

after googling furiously, I've come to that conclusion so I went the direction of creating temporary tables then changing the record source with Me.RecordSource = "Select...FROM TEMPTABLE... Thanks for pointing me in the right direction.

Jerry

 

[Pat Hartman]

I wouldn't say that temp tables are a good solution. Much of the time you can just use queries without actually writing the set of data permananly. Be sure to compact regularly because the process of create/delete or insert/delete cause a database to bloat since Access cannot recover work areas to clean up or reuse unless you compact the database.

What was wrong with replacing the RecordSource with a different SQL string or querydef name?

 

[jerryczarnowski]

Replacing the RecordSource with a different SQL string works just like you had mentioned. I am still trying to figure out how to apply the different SQL string to the current result set which is not a physical table or query. I have an interactive form where the user has eight different combo box controls to choose items from the drop down lists and the application is to update the result set every time the user selects a different item from any of the drop down lists. Sounds like for me to achieve that, I would have to come up with some dynamic SQL and put it into the AfterUpdate event code for each combo box or am I making this more complicated than it needs to be?

Thanks, Jerry