moving in recordset using VBA

[Jhart22889]

Hello all I have a dilemma with some code that creates table entries from multiple subforms. There are three subforms that are based on one table, two subforms are in single form view as they only require one entry per project however the third subform is in datasheet view and has the ability to create several rows associated to one project. In the code the row is created combining all of this information but I cannot figure out how to reset the cursor to start a new row for multiple entries in the third subform. In an example say the three subforms all pertain to purchasing costs. One has y/n questions of overall equipment costs that may be incurred per project, the other has an estimate of overall shipment costs for the project and the third table is the datasheet in which you pick your purchase items and cost of that item. Is it possible to use VBA to call a new row and reset the cursor to that new row for data entry? ANY help is definitely appreciated!:banghead:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Sets the database as well as the file path
cn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Users\Pretend\Documents\Estimate\Access\hypothetical.accdb;"
 
'This gets the public variable FFProjectNumber. FFProjectNumber is created when the Start Date is selected on the front form
 
GetPublicVariable = FFProjectNumber
 
'Opens the desired recordset
rs.Open "tblAdditionalProcurement", cn, adOpenDynamic, adLockOptimistic
 
'Filters the recordset for ProjectIDs that are like the FFProjectNumber
rs.Filter = "ProjectID Like " & FFProjectNumber
 
'Sets the fields in the rs equal to the values that the user input or that was calculated on the top of the procurement field
 
rs.Fields("FedEx") = Me.txtFedEx.Value
rs.Fields("FedExCost") = Me.txtFedExCost.Value
rs.Fields("FedExFee") = Me.txtFedExFee.Value
rs.Fields("PCard") = Me.txtPcard.Value
rs.Fields("PcardCost") = Me.txtPcardCost.Value
rs.Fields("PcardFee") = Me.txtPcardFee.Value
 
'Updates, saves and closes the recordset
 
rs.Update
rs.Save
rs.Close

I use this and a similar code for the two single forms and this code for the datasheet form:

'Dims and sets the connections and the recordsets as new
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
 
'Sets the database as well as the file path
cn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source= C:\Users\Pretend\Documents\Estimate\Access\hypothetical.accdb;"
 
 
'MsgBox FFProjectNumber  The variable was correct when it was passed to this messagebox
 
Dim strProcurementType  As String
 
strProcurementType = cboProcurement
 
 
'Opens the desired recordset
rs.Open "tblProcurementFee", cn, adOpenDynamic, adLockOptimistic
 
 
rs.MoveFirst
'MsgBox strProcurementType
 
Do While Not rs.EOF
    If rs.Fields("ProcurementTransactionName") = strProcurementType Then
    txtFeetotal = rs.Fields("Fee").Value
    rs.MoveNext
    'MsgBox "Part 1 of the loop"
    Else
    rs.MoveNext
    'MsgBox "Part 2 of the loop"
    End If
Loop
 
'MsgBox "You have successfully popped out of the do loop"
 
rs.Close
cn.Close
 
 
End Sub

 

[pbaldy]

Not clear on what you're doing, but

rs.AddNew

will move the recordset to a new record, waiting for the fields to be populated.

 

[pbaldy]

By the way, much more efficient than opening the recordset on the entire table and filtering would be opening the recordset on an SQL statement that included the criteria.

 

[Jhart22889]

To simplify things, the third subform that is used in datasheet form only allows me to put in one record (one row) and doesnt allow me to click in a new row to enter any data in it. I want the code to move the cursor (focus) back to the first column after finishing one row to enter more data.

 

[pbaldy]

Is this what you're trying to do?

http://allenbrowne.com/ser-57.html

 

[Jhart22889]

Not quite, originally I had the information split into two tables, those in the single form view in one and the datasheet view subform information in another....I compiled them because it is similar information all pertaining to purchasing and would help when trying to create the desired reports by each user......the problem is when I combined them the datasheet subform no longer let me enter more than one record (row of data.) what I want to do is have the option to add multiple records associated to one project id (pk) while the other two subforms have one record per project id.....I know that this will create null information but I have that portion figured out.