Reputation: 534
So, I have a MS Access database application. In this application is a main form, which contains a number of subforms. One form in particular has a drop down box that I populate with dates from a database query. When one of these dates is selected, I run a subroutine that is supposed to update a recordset on the subform with history information. Below is some edited code (just removed the large number of fields from the queries)
Private Sub pickdate_AfterUpdate()
'''''''''''''''''''''''''''''''''''''''''
' Add review history by selected date
'''''''''''''''''''''''''''''''''''''''''
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT model, entered_date FROM history WHERE entered_date=#" & Me.pickdate.value & "# ORDER BY model DESC", dbOpenDynaset, dbSeeChanges)
If rs.BOF = False Then rs.MoveFirst
While rs.EOF = False
Forms!main!histories.Form.Recordset.AddNew
Forms!main!histories.Form.Recordset![model] = rs![model]
Forms!main!histories.Form.Recordset![entered_date] = rs![entered_date]
Forms!main!histories.Form.Recordset.Update
rs.MoveNext
Wend
End Sub
I get the error on the Forms!main!histories.Form.Recordset.AddNew
line.
I have tried the following versions of that line:
Forms!main!histories.Form.Recordset.AddNew
main!histories.Form.Recordset.AddNew
histories.Form.Recordset.AddNew
Me.Form.Recordset.AddNew
Me.Recordset.AddNew
Me.AddNew
Me.main!histories.Form.Recordset.AddNew
Me!histories.Form.Recordset.Addnew
Me!main!histories.Form.Recordset.AddNew
I am literally at my wit's end trying to figure out where the issue is. The subform has all the proper boxes to store the information. I have given them labels to match their database columns that will go into them. I've tried setting their control sources to the database column names and not setting them to anything. I've looked up a hundred different "solutions", none of which seem to either fit the problem or work.
I feel like I am overlooking something really easy.
Upvotes: 1
Views: 9664
Reputation: 91376
I reckon you have problems with your names. Check all of them. Do not forget that a subform consists of two parts, the subform control and the form contained. These often have the same name, but not always. In the code you are using, you must have the name of the subform control, not the form contained. If entering data into the subform manually is not working properly, your controls are not bound.
This works for me on a sample table.
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT atext from table1 WHERE akey=21")
If rs.BOF = False Then rs.MoveFirst
While Not rs.EOF '= False
Me.Table1_subform1.Form.Recordset.AddNew
Me.Table1_subform1.Form.Recordset!AText = rs!AText
Me.Table1_subform1.Form.Recordset.Update
rs.MoveNext
Wend
To run a query you could say:
sSQL="INSERT INTO NameOfTable (model, entered_date) " _
& "SELECT model, entered_date FROM history WHERE entered_date=#" _
& Me.pickdate.value & "#"
CurrentDB.execute, dbfailOnError
You can check the sql works in the query design window.
Upvotes: 2