Create New Record on SubForm Programatically

[SodaJim]

Hello,

How can I add a new record in a Subform that is generated from a button on the Parent form...? Then set textboxes on the Subform to values from code...?
Previously, I was able to accomplish this when all the textboxes where on the same form based on the same table; however, I have created a one -> many relationship so I could take a snapshot of the currrent date/time and # hours worked (textbox fields) for each time the employee was called, declined or worked overtime.
I was attempting to utilize the following code snippet to update the Subform fields so they would create a new record and store it in the underlying table...

Private Sub cmdEmployeeDeniedButton_Click()
Dim myMsg As String
Dim otTimeStamp As String
Dim otDate As Date

otTimeStamp = Format(Date, "Long Date") & " " & Format(Time(), "Long Time")
otDate = Forms!frmOvertime![txtOT_Date]

If Forms!frmOvertime![txtOT_Hours] <= 0 Then
myMsg = MsgBox("The Overtime Hours are set to 0" & (Chr(13)) & "Please enter the number of hours!", vbCritical, "Missing Hours")
Forms!frmOvertime!txtOT_Hours.SetFocus
Else
Forms!frmOvertime!frmListByTotalHours!txtTotal_OT_Hours = (Forms!frmOvertime!frmListByTotalHours![txtTotal_OT_Hours] + Forms!frmOvertime![txtOT_Hours])
'Need to create a new record for next variables
'Forms!frmOvertime!frmListByTotalHours!frmOvertime.SetFocus
DoCmd.GoToRecord acActiveDataObject, frmPersonnelOvertime, acNewRec
Forms!frmOvertime!frmListByTotalHours!frmPersonnelOvertime![txtDenied_OT_Date] = otTimeStamp
Forms!frmOvertime!frmListByTotalHours!frmPersonnelOvertime![txtDenied_OT_Hours] = Forms!frmOvertime![txtOT_Hours]
myMsg = "Data has been Modified!"
myMsg = myMsg & (Chr(13)) & "Do wish to Save the Changes?"
myMsg = myMsg & (Chr(13)) & "Click YES to Save or No to Discard Changes."
If MsgBox(myMsg, vbQuestion + vbYesNo, "Save Changes?") = vbYes Then
DoCmd.Save
Me.Requery
Else
DoCmd.RunCommand acCmdUndo
End If
End If

End Sub

I'm also getting a wrong datatype error for the line w/ DoCmd.GoToRecord...
This is where I thought I should create a new record and then fill the fields with the appropriate data...

Any direction appricated,
Jim

 

[WayneRyan]

Jim,

Why not just insert a record into the table and requery the
subform?

' ******************************************
Dim dbs As Database
Dim sql As String

Set dbs = CurrentDb

sql = "Insert into MyChildTable (F1, F2, F3) " & _
"Values ('" & Me.txtBox1 & "', '" & Me.txtBox2 & "', '" & _
Me.txtBox3 & "')"

dbs.Execute(sql)
Forms!MainForm!SubForm.Requery
' *******************************************
hth,
Wayne

 

[SodaJim]

Thanks for the reply Wayne!

If I understand you correctly, I can Insert subtable field data directly from values on a parent form textbox(parent table) as well as Current TimeStamp values instead of setting txtbox values from parent form to subform that way I currently have it...?

If so, can the "Insert" SQL function insert info to more than one table?

Here's a little background to get what's in my head...

tblOvertime - Stores each Overtime info
OT_ID - autonumber
OT_Date - short date
OT_Hours - Number of hours
OT_Authorize - Authorizing LastName
OT_LastName - Person working OT

Parent Table: tblEmployees
EmployeeID - Unique Employee Number
LastName
FirstName
Total_OT_Hours - Cumulative Hours

Child Table: tblEmployeeOT_Stats
OT_Stats_ID - AutoNumber
EmployeeID
Last_OT_Called - Current TimeStamp
Last_OT_Worked - Current TimeStamp
Last_OT_Denied - Current TimeStamp

Main form is frmOvertime based on tblOvertime
Form frmEmployeeOT_Order is on the main form but not bound to it. The form frmEmployeeOT_Order has a child form, frmEmployeeOT_Stats, that is bound by EmployeeID in a one --> many relationship from the tables.

Three buttons, Called, Denied & Worked are located on the frmEmployeeOT_Order which is ordered in ascending order by Total_OT_Hours.
Depending on the button clicked, I need to calculate the new Total_OT_Hours value (Total_OT_Hours + OT_Hours) and store it in tblEmployees, TimeStamp the appropriate field and store in tblEmployeeOT_Stats, and then requery the frmEmployeeOT_Order to reflect the changes in Employee's Total OT Hours.

I hope this give anyone some direction as to what I'm attempting to accomplish and if this can be coded with SQL to Insert data into more than one table at a time...?

Many Thanks again,
Jim

 

[WayneRyan]

Jim,

Can't insert into more than one table at a time.

Just a series of Insert statements . . .

sql = "Insert ..."
dbs.Execute(sql)

sql = "Insert ..."
dbs.Execute(sql)

sql = "Insert ..."
dbs.Execute(sql)

Wayne

 

[SodaJim]

Thanks

Thanks Wayne!

I'll give this concept a try tomorrow...

 

[SodaJim]

Attempting to get this working...

I'm back Wayne...

With regards to the code snippet below:

Code:
-----------------------------------
sql = "Insert into MyChildTable (F1, F2, F3) " & _
"Values ('" & Me.txtBox1 & "', '" & Me.txtBox2 & "', '" & _
Me.txtBox3 & "')"
-----------------------------------

If I utilize a declared variable for one of the values, how should it be coded, the same as you have which is for a textbox value or without the &...?
Secondly, do I have to assign: & "Where tblPersonnelOvertime.EmployeeID = Me.txtEmployeeID" to make sure the association between parent/child tables is maintained or just insert the value of the txtEmployeeID on the current form since this is the employee the user is looking at...
After reviewing this, I would just Insert the EmployeeID from the current form as Me.txtEmployeeID along with the other fields.

Now I'm starting to talk in circles...
I'm still not sure about implementing declared variables in the Insert Statement...?

 

[SodaJim]

More Errors...

Hello again,

I get an error user-defined type not defined for:
Dim dbs As Database

 

[SodaJim]

Anybody Willing to Help...

Hello,

Just looking to solve this problem...
I've tried a few different combinations to no avail
and have no idea on the Dim dbs As Database Error...

Thanks in advance,
Jim

 

[pono1]

Jim,

Are you using Access 2000 or later? If so, I could post some ADO code that may (or may not) help you get back on track.

Tim

 

[SodaJim]

Access 2K...

I recently read that I may have to declare the DB as follows:
Dim dbs As DAO.Database

Additionally, is there a preferred method listed above for inserting new records into a table programatically...

Either utilizing:

sql = "Insert into MyChildTable (F1, F2, F3) " & _
"Values ('" & Me.txtBox1 & "', '" & Me.txtBox2 & "', '" & _
Me.txtBox3 & "')"

Or

Dim dbs as DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = dbs.openRecordset("data table name", dbsOpenDynaset)
rs.AddNew
rs.Fields(0) = GetNextTableID("data table name", "unique field name")
rs.fields(1) = NewData
rs.fields(2) = NewData
rs.Update
rs.Close
dbs.Close
Set rs - Nothing
Set dbs = Nothing

The above code is an example of trying to add a new record. It is not my own, I just pieced it together...

Thanks again,
Jim

 

[pono1]

Jim,

Many on this forum use DAO. Some like writing-out SQL statements, executing them with a Docmd.RunSQL or CurrentDB.Execute command. Most, I imagine, will use whatever works depending on the task, their mood, their knowledge, and the availability of code samples...

With Access 2000, Microsoft started pushing ADO -- here's an ADO sample that will add a record to a subform using a command button. For a clean test I recommend you create a new Access file. Then create two tables, called, say, TblCustomers and TblSales.

Fields in TblCustomers
CustomerID (primary, autonumber)
Name (text)

Fields in TblSales
SaleID (primary, autonumber)
AnyDate (date)
CustomerID (long)

Create two forms, one based on TblCustomers (FrmCustomers), the other on TblSales (FrmSales). Format FrmSales's Default View as a continuous form -- make it a subform to FrmCustomers, making sure the link field is CustomerID.

On FrmCustomers, name the CustomerID control "txtCustomerID."

Test the main- and sub-forms, adding a rec or two.

If the forms work properly, move to design view and create a command button -- don't use the wizard -- on the main form. In the On-Click event of the command button, paste in the code below.

'Note: if no record exists in Main Form
If IsNull(Me.txtCustomerID) Then
    Exit Sub 'Note: leave
End If

'Note: otherwise, set up a variable in memory
Dim lngCusId As Long

'place a copy of the customerid value into the variable
lngCusId = Forms!FrmCustomers.CustomerID.Value

'create and put into memory a copy of the records
'in tblSales that have the same value in their
'customerid field as the current record
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM TblSales WHERE CustomerID = " & lngCusId, _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'manipulate the records in memory
With rst
.AddNew 'add a new record to the recordset
![CustomerID] = lngCusId 'give the new rec the current customerid
.Update 'save this record back to tblSales
End With

rst.Close
Set rst = Nothing

Me.Recalc 'show new rec on form

If all's well, clicking the command button will add a new record to the subform.

Hypothesis: If you can get this to work in a test file, you should be able to do the same in your current application.

Regards,
Tim

 

[SodaJim]

Thanks Tim,

I'll give this a try and see what works best...

 

[SodaJim]

OK...

I get a "compile error: Expected: end of statement" for the following line of code. Access is highlighting the "," (comma) just after "& EmpID":

rst.Open = "SELECT * FROM tblPersonnelOvertime WHERE EmployeeID = " & EmpId, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Code Snippet attached to Click_Event of cmdButton:

Dim EmpID As String
Dim rst As ADODB.Recordset

EmpID = Forms!frmListByTotalHours!EmployeeID

Set rst = New ADODB.Recordset
rst.Open = "SELECT * FROM tblPersonnelOvertime WHERE EmployeeID = " & EmpId, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'manipulate the records in memory
With rst
'add a new record to the recordset
.AddNew
'give the new record the Current EmployeeID
![EmployeeID] = EmpID
'save this record back to tblSales
.Update
End With

rst.Close
Set rst = Nothing
'show new rec on form
Me.Recalc

Any direction to what is causing this error is appreciated. I would like to get to debugging the next stage of this procedure...

Many Thanks,
Jim

 

[pono1]

Arrange your problem chunk of code so that it matches, line breaks and all, the equivalent chunk of code in the sample -- don't forget the underscore ( _ ).

Regards,
Tim

 

[SodaJim]

Thanks Tim ! ! !

Here's the complete sub I'm attempting to utilize for adding a new record into the table "tblPersonnelOvertime".
I need to associate the new record by EmployeeID (txt data type) to maintain 1 --> many relationship with parent table...

There's a few other things in here...
I'm not sure how this will display...

Private Sub cmdEmployeeDeniedButton_Click()
Dim db As ADODB.Database
Dim rs As ADODB.Recordset
Dim myMsg, otTimeStamp, mySQL, EmpID As String
Dim otDate As Date

Set db = CurrentDB
Set rst = New ADODB.Recordset

otTimeStamp = Format(Date, "Long Date") & " " & Format(Time(), "Long Time")
otDate = Forms!frmOvertime![txtOT_Date]

If Forms!frmOvertime![txtOT_Hours] <= 0 Then
myMsg = MsgBox("The Overtime Hours are set to 0" & (Chr(13)) & "Please enter the number of hours!", vbCritical, "Missing Hours")
Forms!frmOvertime!txtOT_Hours.SetFocus
Else
Forms!frmOvertime!frmListByTotalHours!txtTotal_OT_Hours = (Forms!frmOvertime!frmListByTotalHours![txtTotal_OT_Hours] + Forms!frmOvertime![txtOT_Hours])

'Need to create a new record for next variables
myMsg = "Data has been Modified!"
myMsg = myMsg & (Chr(13)) & "Do wish to Save the Changes?"
myMsg = myMsg & (Chr(13)) & "Click YES to Save or No to Discard Changes."
If MsgBox(myMsg, vbQuestion + vbYesNo, "Save Changes?") = vbYes Then

EmpID = Me!EmployeeID

rst.Open = "SELECT * FROM tblPersonnelOvertime WHERE EmployeeID = " & EmpId, _ CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'manipulate the records in memory
With rst
'add a new record to the recordset
.AddNew

'give the new record the Current EmployeeID
![EmployeeID] = EmpID

'save this record back to tblPersonnelOvertime
.Update
End With

rst.Close
Set rst = Nothing

'show new rec on form
Me.Recalc

Else
DoCmd.RunCommand acCmdUndo
End If
End If

End Sub

 

[pono1]

Jim,

Can only offer general advice: break down your code into more meaningful sections, each section devoted to a particular task. (I realize, to some extent, you are already doing this, as you try to tackle this problem.) This will make the code easier to read and understand as you develop and debug. You might want to peruse Help, searching on "subprocedures" and "functions." Regarding the code posted (after a quick look), put together all the lines that create a new rec; put together all the lines that prompt the user with a message box, etc, and do so in the order in which these tasks should take place. Add comments to the sections. And test each section of code separately, making sure it works, only adding another task when you're sure the previous procedure is good.

Good luck with it.

Regards,
Tim

 

[SodaJim]

Thanks again Tim!

I Thank you for the suggestions; however, up until the error mentioned earlier, everything works... I just cannot seem to add a new record into the table "tblPersonnelOvertime" with the Employee ID currently displayed on the main form...

This is the second way I've attempted to add a new record, to no avail...

What is wrong with the following line of code that would yield the error "compile error: Expected: end of statement" ...?

rst.Open = "SELECT * FROM tblPersonnelOvertime WHERE EmployeeID = " & EmpId, _ CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Access is highlighting the comma after "& EmpID"

Anybody...?