Programmatically add new record in subform in datasheet view
I have a form in which code runs to perform an SQL query to select certain records. The resulting recordset should then be presented in a subform of this form.in datasheet view. Code to populate:
Do While Not rs.EOF
Me!Subform![Name] = rs![Name]
... etc
rs.MoveNext
Loop
With this I can have one record displayed on one line in the datasheet view but the next record will overwrite the previous inxstead of starting on a new line.
How do I programmatically add records so the complete result of the query is visible, one on each line?
Regards
Peter
Do While Not rs.EOF
Me!Subform![Name] = rs![Name]
... etc
rs.MoveNext
Loop
With this I can have one record displayed on one line in the datasheet view but the next record will overwrite the previous inxstead of starting on a new line.
How do I programmatically add records so the complete result of the query is visible, one on each line?
Regards
Peter
ASKER
I've tried that but I can only bind to the main form (Me.Recordset = rs) not to Me.Subform.Recordset = rs
how are you building the SQL query?
post the codes here
post the codes here
ASKER
strSql = "Select Name, Address, Age FROM Members WHERE Age > 40;"
Set rs = CurrentDb.OpenRecordset(st
Me.Recordset = rs '<- Gives run-time error Operation is not supported for this type of object
Set rs = CurrentDb.OpenRecordset(st
Me.Recordset = rs '<- Gives run-time error Operation is not supported for this type of object
Try explicitly Dimming rs as a Dao recordset, and using SET:
DIM rs as DAO.recordset
strSql = "Select Name, Address, Age FROM Members WHERE Age > 40;"
Set rs = CurrentDb.OpenRecordset(st
SET Me.Recordset = rs
DIM rs as DAO.recordset
strSql = "Select Name, Address, Age FROM Members WHERE Age > 40;"
Set rs = CurrentDb.OpenRecordset(st
SET Me.Recordset = rs
you can set the recordsource of your subform to table Members
then just set the filter after selecting or entering the criteria
me.subformname.form.filter
me.subformname.form.filter
then just set the filter after selecting or entering the criteria
me.subformname.form.filter
me.subformname.form.filter
The SET statement I suggested might help things syntactically (SET is used with objects), but the recordsource method that cap is suggesting is a better approach.
ASKER
mb: I use Option Explicit so all variables are dimmed. I tried the Set before Me.Recordset and the runtime error message dissapeared. Not filling in the subform though.
cap: How do I set the recordsource to my subform? Me.subform.Recordsource gives an error
cap: How do I set the recordsource to my subform? Me.subform.Recordsource gives an error
peakpeak,
in the design view of your subform, hit F4
this will open the property sheet for your subform
click the Data Tab
in the Record Source row, select table Members
now set the control source for all the controls (textboxes) in your subform to the corresponding field from the Members table
save and close the sub form
in the design view of your subform, hit F4
this will open the property sheet for your subform
click the Data Tab
in the Record Source row, select table Members
now set the control source for all the controls (textboxes) in your subform to the corresponding field from the Members table
save and close the sub form
Try:
Me.subform.Form.Recordsour
mx
Me.subform.Form.Recordsour
mx
ASKER
cap: Your latest comment suggests that I see ALL the members in the subform, not those based on the (dynamic) query. I have an input box in the main form for age and put that value in the SQL statement.
database: Recordsource equal to what? rs won't do.
database: Recordsource equal to what? rs won't do.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
<no points please>
The combination of cap's previous comments will allow you to filter the records at will. The recordsource conains all records, but the filter limits them.
https://www.experts-exchange.com/questions/22631010/Programmatically-add-new-record-in-subform-in-datasheet-view.html#19275644
https://www.experts-exchange.com/questions/22631010/Programmatically-add-new-record-in-subform-in-datasheet-view.html#19274840
The combination of cap's previous comments will allow you to filter the records at will. The recordsource conains all records, but the filter limits them.
https://www.experts-exchange.com/questions/22631010/Programmatically-add-new-record-in-subform-in-datasheet-view.html#19275644
https://www.experts-exchange.com/questions/22631010/Programmatically-add-new-record-in-subform-in-datasheet-view.html#19274840
ASKER
Thanks cap! Finally working!
and bind all the controls to corresponding fields returned by the SQL query