When I run the code below I get a 3078 error that it can't find the table or query. It is happening at this line:
strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES (Me.CLIENT_ID)"
CurrentDb.Execute strSQL1, dbFailOnError
If the answer to the msgbox is yes, than I want it to create a new record in tbl_HISTORY and then display CLI_HISTORY_subform using that new record. Can anyone tell me why I'm getting this error?
Dim strSQL As String DoCmd.Hourglass True Application.Echo False, "Processing ..." With Me Select Case tabClients Case 0 ' First TabPage (General) ' Do nothing. SourceObject loaded. Case 1 ' Second TabPage (Academic) 'Load source objects on Academic tab If .stuSchedule_subform.SourceObject <> "frm_STU_SCHEDULE" Then .stuSchedule_subform.SourceObject = "frm_STU_SCHEDULE" End If If .academics_subform.SourceObject <> "frm_STU_ACAD" Then .academics_subform.SourceObject = "frm_STU_ACAD" End If Case 2 ' Third TabPage (History) 'Find client history. If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY" Else 'If no history is available, prompt user to add client history. If MsgBox("No client history available. Would you like to add it now?", vbYesNo) = vbYes Then 'Insert a new record into in tbl_CLI_HISTORY strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES (Me.CLIENT_ID)" CurrentDb.Execute strSQL1, dbFailOnError 'Find new record and display as subform If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY" End If End If End If Case 3 ' ... Case 4 ' ... ' ... End Select End With DoCmd.Hourglass False
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
mkelly2384,As cap1 mentioned, the table/column names must match exactly. For example, a stray leading or trailing space in a name can muck things up.Also, please post the exact syntax you tried to use, and confirm whether CLIENT_ID is numeric or text. It would not shock me if the lack of a space between your table name and the open parenthesis for your column list upsets Access, which is why I included a space there. (SQL Server would not have "cared" about that.)Patrick
mkelly2384,As I asked in my last previous post:>>Also, please post the exact syntax you tried to use, and confirm whether CLIENT_ID is numeric or text.Patrick
If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then
.CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"
Else
0
mkelly2384Author Commented:
Oh sorry...CLIENT_ID is numeric.
Is there a better way to do this?
Private Sub tabClients_Change()On Error GoTo tabClients_Change_Err Dim strSQL As String DoCmd.Hourglass True Application.Echo False, "Processing ..." With Me Select Case tabClients Case 0 ' First TabPage (General) ' Do nothing. SourceObject loaded. Case 1 ' Second TabPage (Academic) 'Load source objects on Academic tab If .stuSchedule_subform.SourceObject <> "frm_STU_SCHEDULE" Then .stuSchedule_subform.SourceObject = "frm_STU_SCHEDULE" End If If .academics_subform.SourceObject <> "frm_STU_ACAD" Then .academics_subform.SourceObject = "frm_STU_ACAD" End If Case 2 ' Third TabPage (History) 'Find client history. If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY" Else 'If no history is available, prompt user to add client history. If MsgBox("No client history available. Would you like to add it now?", vbYesNo) = vbYes Then 'Insert a new record into in tbl_HISTORY strSQL = "INSERT INTO tbl_HISTORY (CLIENT_ID) VALUES (" & Me.CLIENT_ID & ")" CurrentDb.Execute strSQL1, dbFailOnError 'Find new record and display as subform If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY" End If End If End If Case 3 ' ... Case 4 ' ... ' ... End Select End With DoCmd.Hourglass False
rivate Sub tabClients_Change()On Error GoTo tabClients_Change_Err Dim strSQL As String DoCmd.Hourglass True Application.Echo False, "Processing ..." With Me Select Case tabClients Case 0 ' First TabPage (General) ' Do nothing. SourceObject loaded. Case 1 ' Second TabPage (Academic) 'Load source objects on Academic tab If .stuSchedule_subform.SourceObject <> "frm_STU_SCHEDULE" Then .stuSchedule_subform.SourceObject = "frm_STU_SCHEDULE" End If If .academics_subform.SourceObject <> "frm_STU_ACAD" Then .academics_subform.SourceObject = "frm_STU_ACAD" End If Case 2 ' Third TabPage (History) 'Find client history. If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY" Else 'If no history is available, prompt user to add client history. If MsgBox("No client history available. Would you like to add it now?", vbYesNo) = vbYes Then 'Insert a new record into in tbl_HISTORY with currentdb.openrecordset("tbl_History") .addnew !Client_ID=Me.Client_ID .update end with ' strSQL = "INSERT INTO tbl_HISTORY (CLIENT_ID) VALUES (" & Me.CLIENT_ID & ")" ' CurrentDb.Execute strSQL1, dbFailOnError 'Find new record and display as subform If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY" End If End If End If Case 3 ' ... Case 4 ' ... ' ... End Select End With DoCmd.Hourglass False
Rather than inserting, I first tried to do something like this:
DoCmd.SelectObject "CLI_HISTORY_subform"
DoCmd.GoToRecord acActiveDataObject, , acNewRec
but that didn't work. Didn't know if I could create a new record in a subform anyway. Would that be better than inserting if it was done right?
0
mkelly2384Author Commented:
Yay--capricorn1--you're a genius! Thanks so much--worked perfectly.