Form and query issue

[Fay]

I have this form that currently has 3 subforms on it. As the person steps through the process and commits to items on the current form the next form is displayed. My problem is I keep getting a Run-time error 3211: The database engine could not lock table "tbl_CL_MakeSchStep2 because it is already in use by another person or process. Here is my code. How do I deal with this type of problem? I turned the default warning back on and it steps through as it should but stops on the strSQL statement. I should note that the subform frm_CL_SchSort is tied to the qry_CL_MakeSchStep2 which is based on the tbl_CL_MakeSchStep2. Thank you for any help you have to offer. Fay

Private Sub cmdSaveComp1_GotFocus()
Dim strSQL As String

'Turn off Warnings
'DoCmd.SetWarnings False
'Pushes the appropriate class schedule components to tbl_CL_Sch_ClassSchComp for further processing
If frm_CL_Sch_SF_SelectComp.Form.ckRegistration = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [SortOrder], [AlternateTitle])" & _
"VALUES ('Registration',15, 1, 'Registration'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckWelcome = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [SortOrder], [AlternateTitle])" & _
"VALUES ('Welcome',5, 2, 'Welcome'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckMBreak = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [AlternateTitle])" & _
"VALUES ('Morning Break', 15, 'Morning Break'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckLunch = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [AlternateTitle])" & _
"VALUES ('Lunch', 60, 'Lunch'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckABreak = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [AlternateTitle])" & _
"VALUES ('Afternoon Break',15, 'Afternoon Break'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckQA = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [CountsAsCE], [AlternateTitle])" & _
"VALUES ('Q and A', 10, True, 'Q and A'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckQAPanel = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [CountsAsCE], [AlternateTitle])" & _
"VALUES ('Q and A Panel',30, True, 'Q and A Panel'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckEvaluation = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event], [Length], [CountsAsCE], [AlternateTitle])" & _
"VALUES ('Evaluation', 5, True, 'Evaluation'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckOG1 = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event])" & _
"VALUES ('Objective Group 1'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckOG2 = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event])" & _
"VALUES ('Objective Group 2'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckOG3 = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event])" & _
"VALUES ('Objective Group 3'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckOG4 = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event])" & _
"VALUES ('Objective Group 4'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckOG5 = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event])" & _
"VALUES ('Objective Group 5'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckOther = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event])" & _
"VALUES ('Other'); "
End If
If frm_CL_Sch_SF_SelectComp.Form.ckOther1 = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([Event])" & _
"VALUES ('Other'); "
End If

'Runs SQL statement to update the ClassID that is in a separate control on the frm_CL__Sch_SelectComp
'pulled value is then updated into SET tbl_CL_Sch_ClassSchComp.ClassID
DoCmd.RunSQL "UPDATE tbl_CL_MakeSchStep1" & _
" SET tbl_CL_MakeSchStep1.ClassID = [Forms]![frmMain]![frm_CL_Classes].[Form]![txtClassID];"


'Create a table that is a copy of tbl_CL_MakeSchStep1 so I can tie a query to the Step2 table
strSQL = "SELECT tbl_CL_MakeSchStep1.ObjectivesID, tbl_CL_MakeSchStep1.ClassID, tbl_CL_MakeSchStep1.SortOrder, " & _
"tbl_CL_MakeSchStep1.Objective, tbl_CL_MakeSchStep1.Length, tbl_CL_MakeSchStep1.CountsAsCE, tbl_CL_MakeSchStep1.Day, " & _
"tbl_CL_MakeSchStep1.Inactivate, tbl_CL_MakeSchStep1.ScheduleID, tbl_CL_MakeSchStep1.Event, tbl_CL_MakeSchStep1.AlternateTitle, " & _
"tbl_CL_MakeSchStep1.AddEvent INTO tbl_CL_MakeSchStep2 " & _
"FROM tbl_CL_MakeSchStep1 " & _
"ORDER BY tbl_CL_MakeSchStep1.SortOrder;"

DoCmd.RunSQL strSQL


'Display apporpriate form and box


frm_CL_Sch_ClassObjListing.Visible = False
frm_CL_Sch_SF_SelectComp.Visible = False
frm_CL_SchSort.Visible = True

bxObjectives.Visible = False
bxComp.Visible = False
bxSort.Visible = True

'Restarts Access standard warnings.
'DoCmd.SetWarnings True
End Sub

[June7]

Why are you using INSERT sql action to create records as opposed to direct data entry into bound form?

The UPDATE action will be applied to every record in the table. However, the reference to form control is a variable. Variable should not be within quote marks but should be concatenated:

" SET tbl_CL_MakeSchStep1.ClassID = " & [Forms]![frmMain]![frm_CL_Classes].[Form]![txtClassID]


Why are you programmatically making a table?

Incorporating a process that routinely modifies db design is usually a bad idea.

[Fay]

Okay. I am on a major learning curve. I actually figured out how it get the material into the table in the proper way using this code.

If frm_CL_Sch_SF_SelectComp.Form.ckRegistration = True Then
DoCmd.RunSQL "INSERT INTO tbl_CL_MakeSchStep1 ([ClassID], [Event], [Length], [SortOrder], [AlternateTitle])" & _
"VALUES ([Forms]![frmMain]![frm_CL_Classes]![txtClassID],'Registration',15, 1, 'Registration'); "
End If

Let's see if I can lay it out for you.

The objectives comes from one table. The events come from a form where they pick what they want and with the Private Sub cmdSaveComp1_GotFocus() function the chosen items are added to the table with the selected objectives. I could have had the people pick from drop-downs, but I wanted the picking to be easier. Hence the form and the combining of information.

I tried with making a query off of the tbl_CL_MakeSchStep1 to use on another subform but got an error message when I bound the form to that query. I get a Run-time error 3211: The database engine could not lock table "tbl_CL_MakeSchStep1 because it is already in use by another person or process. So I was hoping if I made a second table off the first table and queried the 2nd table it would work. But it didn't. Any ideas? Thank you for you time. Fay

[June7]

Hard to follow.

If you tried to bind multiple forms to the same data, that could cause the table locking issue.

You are using check boxes instead of a single combobox? Did you put the checkboxes in an Option Group?

Regardless, I still don't see why using INSERT and UPDATE instead of just entering data into current record of bound form. That could be done with checkbox Option Group.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Fay]

Pick a class except Up and Running from the class locator.
Click on the Objectives tab, Create Schedule tab, then Getting Started. At this point you should see several objectives listed. Click the Commit to Objectives button. Click yes to the message box. At this point I get a run-time error 3211.

How do I avoid this error. The next step is to select and commit to the schedule components on frm_CL_Sch_SF_SelectComp, but that page does not show. The design is after picking and committing to the components those items are now grouped into tbl_CL_MakeSchStep1 with the objectives. There is a query by the name tbl_CL_MakeSchStep11 attached to the next form which is frm_CL_SchSort.

In my original post I had used a tbl_CL_MakeSchStep2 trying to avoid that error that didn't work either. Do need to switch to a separate independent form instead of a subform?

One other issue on the Objectives and Content tab there are navigation buttons that don't work. If you look at the first two classes all buttons are dimmed out. But if you go to the Up and Running class which has no entered objectives the Next and Last buttons are available. What have I messed up?

Thank you. I hope this helps.
Fay

[June7]

I just noticed your SQL to 'make a table' isn't. The SQL is just a SELECT query. RunSQL is only good for action SQL (INSERT, UPDATE, DELETE, MAKE, SELECT INTO). SELECT alone is not an action. Try adding the INTO keyword.

However, a better approach is to create the table and leave it permanent. Then INSERT SELECT and DELETE records. This is how a 'temp' table should be managed. In a multi-user split db, the temp table would be in the frontend. Either way, a temp table will not work in a multi-user db that is not split.

Why do ClassType and TypeOfCE comboboxes not have RowSource? If I click on TypeOfCE when the form first opens, I get error.