Insert first record into empty record set (based on query) (1 Viewer)

[Rx_]

A form uses a List Box that populates with a query. The query uses another forms ID that is selected. The record set (filterd by the ID) populates the list box. The records in the list box can be updated. It is possible to insert a new record in the list box, refresh the query and have it appear.

Problem - The query returns an empty recordset.
The record set recgonizes the field names, types. But, there are zero records. Try to insert a new record and it errors. And still shows zero recordcount.

1300            Set rs = Me.Recordset

1340            rs.AddNew ' clears fields add any data, update - ID 99999 is just a place holder  Field 0 is an autocounter
1350     Debug.Print "Record count is " & rs.RecordCount '  (zero returned)                       Debug.print rs.fields(1).name  ' Yes the field is named "ID" 1360                rs.Fields("ID").Value = 99999
1370                rs.Update
1380                strAddNewRecordBookmark = rs.Bookmark

 

[vbaInet]

There might be other ways to achieve this. What is your overall goal? Update the combo box on Form B after a new record in Form A has been inserted?

 

[Rx_]

I am just trying to add a first record to the underlyhing empty record set that populates the list box. When the first record is inserted, the users complete a form and save, then the list box will display the results.

Was looking at some Insert code - Would probably have to
NewData = Replace(NewData, "'", "''")

strSQL = "INSERT INTO Wells ( id ) " & _
"SELECT '" & NewData & "' AS Wells;"
DBEngine(0)(0).Execute strSQL, dbFailOnError

 

[vbaInet]

I think you might be making things too complex by using a recordset to add to the list box's underlying record source. As long as the listbox is based on the table you're adding to it will automatically including the records. Sometimes all you need is a Refresh command.

So yes, the INSERT statement is the way to go. Here's a link:
http://www.techonthenet.com/sql/insert.php

 

[Rx_]

Right on, the listbox is driven off of the recordset, so just disregard that there is a list box. I am just finding there is different code for a form's record set depending on if there are zero records or more than zero.
For zero recordcount, this code worked

1300            Set rs = Me.Recordset
1310
1340            rs.AddNew 
1350      Debug.Print "Record count is " & rs.RecordCount ' zero here
' then add data to two drop list boxes tied to two of these fields
' press an update button that runs this code
1500   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
1600  rs.Update
1610  rs.MoveLast                     ' move to new record
1620            rs.Edit
1630            rs.Fields(1).Value = intMyID
1640            rs.Update
' Now I have added a new record with three field values - two by list box, one by variable
'Note, the Rs.Update would not work until the DoCmd above was run.
' Inother words, the rs.Update would fail on the rs.Edit if it was run first
' the DoCmd statement was not necessary when the record set has one or more records in it

 

[vbaInet]

Seems you're still back to your initial way of doing things. Your code is wrong. Like I mentioned if all you're doing is adding data to a table (regardless of which control uses it as it's recordsource) then you don't need a Recordset. Recordsets are used for much complex tasks. I had suggested using an Insert query.

A DoMenuItem command does not affect a recordset because a recordset is an object entirely independent of that command.