Full Version: Insert Into Works Then Doesn't!
UtterAccess Forums > Microsoft® Access > Access Forms
I have the following code in the Sub for a command button that closes my form (a patient admisson form) - actually makes it invisible so I can retrieve data for the next called form. The idea is to generate a new record in 4 other tables since all of them will be accessed at some stage (all new patients will have data written into the 4 tables at some stage before being discharged).
Private Sub cmdUsePatient_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
strSQL = "INSERT INTO Data_2_Summary (AdmitID) VALUES ('" & me.txtAdmitID.Value "')"
CurrentDB.Execute strSQL
strSQL = "INSERT INTO Data_3_AS (AdmitID) VALUES ('" & me.txtAdmitID.Value "')"
CurrentDB.Execute strSQL
strSQL = "INSERT INTO Data_4_Discharge (AdmitID) VALUES ('" & me.txtAdmitID.Value "')"
CurrentDB.Execute strSQL
strSQL = "INSERT INTO Data_5_Tasks (AdmitID) VALUES ('" & me.txtAdmitID.Value "')"
CurrentDB.Execute strSQL
Me.Visible = False
DoCmd.OpenForm "frmReferralData", acNormal, , AdmitID = Me.txtAdmitID.Value, acFormAdd
End Sub
When I check the 4 tables for the new record the first 3 have the new record with the AdmitID value inserted but the 4th one (Data_5_Tasks) doesn't!!! The Me.Visible and the DoCmd.OpenForm codes work fine.
I am wondering is there something in the properties of a table that might prevent the INSERT INTO command working as I can't see any other reason the command doesn't work!
Jeff B.
Before proceeding down this path, bear in mind that creating "dummy" records in tables is not necessarily good database design. Although you know (in advance) that you will have those, why create empties when you can simply create a new record when someone has data to enter?
I started out that way but the 4 tables in question must be completed on or before a certain time but this time changes depending on other factors. I had a table with flagged data for each of the tables (flagged as either completed or not) which are repeatedly queried by using the timer function but what I want to do is create a new record initially and assigned the flagged status directly into them (default is not completed so I don't need to specify this in the insert into string as it is automatically entered when the new record is generated). If I can't get this to work i will revert back but out of interest i still don't understand why the first 3 executes work and the last one doesn't.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.