New Record button to advance 9 subforms 2

[dbar10]

This main screen is for authorizations for service with 9 subforms. Each subform holds times for a particular service completed on that day.It works great but every time I add a new record to one I have to advance all the other subforms manually to stay in sync with it.

I want to out a command button on my main form to give me a new record in all the subforms at once. I have looked all over and cannot find any help on this. Can you help?

Main Form:
Authorization Client Form

Subforms:
HCWeekHoursbfrm
PCWeekHours Subform
APCWeekHourssbfrm
RespWeekHourssbf
LPNWeekHrssbf
RNWeekHoursSbfrm
PTWeekHoursSbfrm
OTWeekHoursSbfrm
SLTWeekHoursSbfrm

 

[NXMold]

Create an appropriate sql syntax (this somewhat assumes that you are inserting some information, not a completely blank record) for each table, put code such as this behind your button.



Private Sub btnAddNew_OnClick()

CurrentDb.Execute "INSERT INTO ... tbl1 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl2 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl3 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl4 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl5 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl6 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl7 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl8 ...", dbFailOnError
CurrentDb.Execute "INSERT INTO ... tbl9 ...", dbFailOnError

End Sub

 

[dbar10]

Thank you very much. I would not have thought of using this. I appreciate your help.

 

[dbar10]

I put this in the command button:

Private Sub Command74_Click()
CurrentDb.Execute "INSERT INTO Me.[HCWeekHourssbfrm]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[PCWeekHours Subform]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[APCWeekHourssbfrm]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[RespWeekHourssbf]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[LPNWeekHoursSbfrm]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[RNWeekHoursSbfrm]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[PTWeekHoursSbfrm]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[OTWeekHoursSbfrm]", dbFailOnError
CurrentDb.Execute "INSERT INTO Me.[SLTWeekHoursSbfrm]", dbFailOnError

End Sub

But it says that there is a syntax arror on the Insert into statement. Can you help me

 

[PHV]

The correct syntax is:
INSERT INTO TableName (numField,strField,dateField) VALUES (numValue,'string value',#yyyy-mm-dd#)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NXMold]

Just to elaborate on PHV's post and show specifically how to add variable data to the sql string:

CurrentDb.Execute "INSERT INTO TableName (numField,strField,dateField) VALUES (numValue,'" & [variable] & "',#yyyy-mm-dd#);", dbFailOnError

You are inserting a record directly into the tables, you cannot insert into a form. After doing the insert you will need to requery the main form so the new records become visible.

To help you build the correct sql syntax, we would need to know the table names and field names you are trying to set. Maybe provide an example from one of the subforms (form recordsource and controlsource for any fields you want to specify up front).

 

[dbar10]

Thank you for your help.

HCWeekHourssbfrm
Tables are PayerAuthtbl one to many HCWeekHours with PayAutID is joined.
Fields to fill in HCWeekHours would be PayAuthID from [Forms]![Authorization Client Form]![payAuthID] and ClientID from [Forms]![Authorization Client Form]![ClientID] and FromDate = Date(). That should do it.

Here's the rest of the tables

[PCWeekHours Subform] - Table= PCWeekHours
APCWeekHourssbf - Table is APCWeekHours
RespWeekHourssbf - Table is RespWeekHours
LPNWeekHrssbf - Table is LPNWeekHours
RNWeekHoursSbfrm - Table is RNWeekHours
PTWeekHoursSbfrm - Table is PTWeekHours
OTWeekHoursSbfrm - Table is OTWeekHours
SLTWeekHoursSbfrm - table is SLTWeekHours

main Form is [Authorization Client Form]

Really appreciate your help.

 

[dhookom]

Do you really have separate tables for different types of hours? I so, have you considered using one table with a field to store the hour type?

Your request assumes there are many types of hours for each clientid? Are there always OT and SLT hours or are you creating unrequired records in tables?

Duane
Hook'D on Access
MS Access MVP

 

[NXMold]

Try this for the first table, it fills in two fields in the HCWeekHours table.
Once this is working, maybe you can see what needs to be tweaked for the other eight tables.



Private Sub Command74_Click()
Dim strAuthID as string

strAuthID = [Forms]![Authorization Client Form]![payAuthID]

CurrentDb.Execute "INSERT INTO HCWeekHours (payAuthID,FromDate) VALUES (" & strAuthID & ",Date());", dbFailOnError

me.requery

end sub

 

[dbar10]

Yes, I know it seems complicated but because of the way that this scheduling section syncs with billing at has to be done this way. Each Client could have any number of combinations of services on any given day. The program needs to see all choices every day even if they are not used. It's Medicaid billing and it gets very complicated.

I had considered one table but because there are 15 different funding sources with different rates paid for services and billing and reconciliation functions. Everything has to be broken way down. I have been working on this for a year and a half. Billing, Clients, Workers, Clinical, HR, Payroll and Scheduling are all linked. At this point I would start a huge domino effect if I change this setup. Thanks anyway.

 

[dbar10]

thank You NXMold,

I put this in. It ran but the new record did not appear in the subform. I suspect it needs the ClientID, so I tried to add that but I don't understand the syntax. Now I'm getting errors. Can you help me fine tune? Thanks

Dim strAuthID As String

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
CurrentDb.Execute "INSERT INTO HCWeekHours (payAuthID,ClientID,FromDate) VALUES (" & strAuthID & " & strClientID & ", Date()); ", dbFailOnError"

Me.Requery

 

[dhookom]

You suggest AuthID and ClientID are string/text. If so, try something like:

Dim strAuthID As String
Dim strClientID As String
Dim strSQL as String

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO HCWeekHours (payAuthID,ClientID,FromDate) " & _
  "VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date() & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

That deserves a big star. Thanks DHookum. That worked perfect. I wish I understood VBA better. Although I have learned a ton on this project and with you guys help. Keep up the good work.

Do I just keep repeating this with all the tables?

 

[dbar10]

Just repeated your statement and it works great. Thanks again

 

[NXMold]

It can be tough to get the SQL syntax just right, I'll often copy it and paste into a new query because the query builder sometimes points out mistakes that are harder to track down in vba.

You'll have to repeat this for the each of your tables.