Adding successive records 1

[Amadea]

I have a form that does two things, (1) finds a record that matches the selection in a combox and (2) provides the input boxes to add a new record to one or two tables, depending on the criteria evaluated. It's all working except for one thing. I can add the first new record, but when I enter the data for the next new record it is not added. I get an "ADD SUCCESSFUL" message in either case, which means I've got my message set up wrong (well, I guess that thing 2 that wrong). But I can't figure out why I can't add more than one record in a row.

Now, if I chose an item from the combobox and then try to add a second record, that works. If I close the screen (form) and reopen it, that works. But I can't just keep filling in the input boxes and clicking "ADD", one right after another.

I'll post the code if necessary. It's part of the onclick event of a command button. I'm using INSERT INTO sql to add the new records.

Thanks for any and all advice!

Amadea

 

[TheAceMan1]

How are ya Amadea . . . . .

Post the code and the relationships of the tables . . .

See Ya! . . . . . .

 

[Amadea]

Well, here's the code. I'm trying to accommodate the situation where a user may want to add more than one new instructor to the database. Most of the time they are adding one person.

Private Sub cmdAddInstr_Click()
On Error GoTo Err_cmdAddInstr_Click

' Check for missing required data
If IsNull(Me!txtInstr) Then
If MsgBox("Please enter an instructor name abbreviation." _
& Chr(13) & Chr(10) _
& "Example: EINSTEIN A A" _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required selection is blank.") = _
vbCancel Then
Exit Sub
End If
ElseIf IsNull(Me!txtLName) Then
If MsgBox("Please enter the instructor last name." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required selection is blank.") = _
vbCancel Then
Exit Sub
End If
ElseIf IsNull(Me!txtFName) Then
If MsgBox("Please enter the instructor first name." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required field is blank.") = _
vbCancel Then
Exit Sub
End If
ElseIf IsNull(Me!cboCategory) Then
If MsgBox("Please select the instructor category." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required field is blank.") = _
vbCancel Then
Exit Sub
End If
Else

Dim strSQL As String
Dim strSQL2 As String

strSQL = "INSERT INTO InstrName (DivNum, EmpID, Instr, LName, FName, MI, Category, Status, Step)" & _
"VALUES ('" & DivNum & "', ' ', '" & txtInstr & "', '" & txtLName & "', '" & txtFName & "', '" & txtMI & "', '" & cboCategory & "', 'ACTIVE', ' ');"

strSQL2 = "INSERT INTO OfficeHours (DivNum, Instr, Office, Phone, Ext1, Ext2, Hours, Print)" & _
"VALUES ('" & DivNum & "', '" & txtInstr & "', '', '', '', '', '', '');"

' Evaluate: full time instr is added to both tables; part time instr is added only to InstrName table
If Me!cboCategory = "FT" Then
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.RunSQL (strSQL2)
DoCmd.ShowAllRecords
DoCmd.Requery "cboInstrName"
Else
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.ShowAllRecords
DoCmd.Requery "cboInstrName"
End If

If MsgBox("Add Successful!" _
& Chr(13) & Chr(10) & Chr(10) _
& "Geez...you're good. Check the drop down list " _
& Chr(13) & Chr(10) _
& "for your added instructor name.", _
vbOKOnly, "Add outcome.") = _
vbOK Then
DoCmd.GoToControl "cboInstrName"

' Reset the ADD Instructor fields to blank content
Me!txtInstr.Value = Null
Me!txtLName.Value = Null
Me!txtFName.Value = Null
Me!txtMI.Value = Null
Me!cboCategory.Value = Null
End If

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "Hide" Then ctl.Visible = False
Next ctl

Exit_cmdAddInstr_Click:
Exit Sub

Err_cmdAddInstr_Click:
MsgBox Err.Description
Resume Exit_cmdAddInstr_Click
End If

End Sub

 

[Amadea]

The relationship to the two tables is a field named Instr and it's a one-to-many relationship. Thanks for your insight.
Amadea

 

[TheAceMan1]

Amadea . . . . .

Sorry to get back so late (Church duties), and its late now.

I'll give it a good critique tomorrow . . . . . can barely keep my eyes open . . . . . .

See Ya! . . . . . .

 

[TheAceMan1]

OK Amadea . . . . .

Wow . . . . there's quite a bit to sort out here. Answer all questions:

What version Access?

In the relationship, is [blue]Referential Integrity[/blue] turned on?

In the relationship, InstrName is [blue]on the one side[/blue], OfficeHrs [blue]the many[/blue]. Is this correct?

Your saying one [blue]InstrName can have many OfficeHrs[/blue]?
(Your table structure appears as if it should be one to one.)

Which fields are the [blue]PrimaryKeys[/blue] of the tables, and what are their [blue]data types[/blue]? Use the following format of [purple]TableName - PrimaryKeyName - DataType[/purple] to post them (do the same for Instr). Examples:

[purple]InstrName - DivNum - AutoNumber
OfficeHrs - DivNum - Text[/purple]

Are the controls on the form [blue]bound[/blue] or [blue]unbound[/blue]?
(I believe unbound)

Now . . . . [purple]Instr[/purple] is an VBA Function Name and needs to be changed. [purple]Make it so![/purple]

[blue]but when I enter the data for the next new record it is not added. I get an "[purple]ADD SUCCESSFUL[/purple]"[/blue]

Unless some data is missing (which you check for), your routine always displays "[purple]ADD SUCCESSFUL[/purple]" because its simply a part of the routine. [blue]You don't check to see if the data was actually appended[/blue]. So, as it is, this is normal for you.

Need to make changes to the SQL, but need this info 1st.

See Ya! . . . . . .

 

[Aerin]

Hi Amadea,

If I understand you properly, you want to use one form to view the data from the table and to add new information? Is your form based on InstrName table?

if you have the form showing all instructors' details and combo box where you select the instructor to view his data, I suggest you to add a button for adding new record to the InstrName and after update event to the category combo.
I will try to "draw" it

Form with record source based on InstrName
----------------------------------------------------------
__________________
Select Instructor: |________________|>|
_________ ____________
Last Name: |_________| First Name:|____________|
_________ _________________
Category: |_________|>| |Add New Instr Btn|
|_________________|
| Save Btn |
|_________________|
-----------------------------------------------------------

Combo Select Instructor based on the InstrName table as well and show all details, hope it is clear.

AfterUpdate event for Select Instructor is finding the target instructor and shows details in fields:
--------------------------------------------------
Private Sub SelectInstructor_AfterUpdate()
On Error Resume Next
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & str(Nz(Me![SelectInstructor], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Onclick event for the Add Button:
-----------------------------------------------
Private Sub Command6_Click()
On Error GoTo Err_Command6_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

You will not need to clear boxes and run query to add the record.

And checking for the integrity of data ( I will take your code and modify it ) Just don't forget to add error checking

You could put it to form event or save button click event

Private Sub Form_BeforeUpdate(Cancel As Integer)


' Check for missing required data
If IsNull(Me!txtInstr) Then
If MsgBox("Please enter an instructor name abbreviation." _
& Chr(13) & Chr(10) _
& "Example: EINSTEIN A A" _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required selection is blank.") = _
vbOk Then
CANCEL = true
Exit Sub
End If
ElseIf IsNull(Me!txtLName) Then
If MsgBox("Please enter the instructor last name." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required selection is blank.") = _
vbOk Then
Cancel = true
Exit Sub
End If
ElseIf IsNull(Me!txtFName) Then
If MsgBox("Please enter the instructor first name." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required field is blank.") = _
vbOk Then
Cancel = true
Exit Sub
End If
ElseIf IsNull(Me!cboCategory) Then
If MsgBox("Please select the instructor category." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required field is blank.") = _
vbOk Then
Cancel = true
Exit Sub
End If
'If user pressed cancel, undo all typing
cancel = true
me.undo
'After then here you could put your actions like go to the first record or last or whatever

End Sub

Private Sub Form_AfterUpdate()
msgbox "Added successful"

if me.combocategory = "F" then
'Add your second query here
.......
end if

End Sub

To activate form before and after update events you need to save the record, so you could save it after the last item or put the event to the save button
docmd.runcommand accmdsaverecord


Ok, if it is interesting but not clear let me know

 

[Amadea]

Busy day and I'm just getting to your kind responses. Here are the answers:

Access 2000 ver9.6+
No referential integrity because both tables do not have matching records. IOW, InstrName includes full time and part time instructors; OfficeHours only has records for full time instructors. But the relationship is one-to-many. However, when I drag Instr from InstrName to OfficeHours to create the relationship, OfficeHours ends up on the one side and InstrName on the many side. Ooo, maybe that's because OfficeHours has a primary key (Instr) but InstrName does not.

Table name: InstrName
DivNum
EmpID
Instr
LName
FName
MI
Category
Stauts
Step (Num)

Table name: OfficeHours
DivNum
Instr
Office
Phone
Ext1
Ext2
Hours (memo)
Print

The form (based on the InstrName table) has a combo box to select an instructor and show current info and allow editing. There are also unbound text boxes to enter new info for the added record: Instr, LName, FName, MI, Category, Status. The remaining table field info is supplied by hidden text boxes on the form or the info is left blank, which is okay. This form has been functioning well for editing and adding records to the InstrName table.

I've been asked to develop an instructor office hours component to the original database and I wanted to use this form to add a new record to OfficeHours when the new instructor is a full time instructor.

Where do I use Instr that gets confused with the VBA function name?

So, I've set up the message wrong. I'd like to fix that so it functions more appropriately; only displays "Add successful" when that is really true, etc.

There is an "Add Record" button whose On Click event triggers the adding. I just don't get why I can't add two new records in a row. Do I also need a save record command in there also??

Maybe the INSERT INTO is not the best approach. Boy, do I need help. Thanks!
Amadea

 

[Amadea]

Well, I did a little testing out again and things seem to work now. Am I just imaging this?!? I omitted the docmd.showallrecords because I failed to realize that it removed the filter I have set for the form. Can't do without the filter. And I added a DoCmd.RunCommand acCmdSaveRecord line, so now that part looks like this:

' Evaluate: full time instr is added to both tables; part time instr is added only to InstrName table
    If Me!cboCategory = "FT" Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL)
        DoCmd.RunSQL (strSQL2)
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Requery "cboInstrName"
    Else
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL)
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Requery "cboInstrName"
    End If
    
    If MsgBox("Add Successful!" _
        & Chr(13) & Chr(10) & Chr(10) _
    & "Geez...you're good. Check the drop down list " _
        & Chr(13) & Chr(10) _
    & "for your added instructor name.", _
        vbOKOnly, "Add outcome.") = _
        vbOK Then
        DoCmd.GoToControl "cboInstrName"

    ' Reset the ADD Instructor fields to blank content
    Me!txtInstr.Value = Null
    Me!txtLName.Value = Null
    Me!txtFName.Value = Null
    Me!txtMI.Value = Null
    Me!cboCategory.Value = Null
    End If

I'm still interested in the message that I have set up wrong. I guess I need to add something about checking to see if the record was added then do the appropriate message? Not to mention how deeply grateful I would be on any help in improving the coding for this form. This whole database will soon be set up for multiple users on a network.

Amadea

 

[TheAceMan1]

Amadea . . . . .

I was gonna take care of your messaging earlier. Its a little late so I won't be able to get it all in. But you can prepare for tomorrow with code below and see what the messages look like, besides . . . . I have a few questions before completeing your code. Anway to the messaging. . . .

Using a well know [blue]EVal Function[/blue] in Access, a neat style of displaying MsgBox (in old Access 97) can be achieved with its successors. Access 2000 and later got rid of the display format. I created my own function [purple]uMsg[/purple], using Eval to achieve the format ([blue]its really cool compared to present display format[/blue]). A small critique:

You can use the [purple][b]uMsg[/b][/purple] function to create a formatted error message similar to built-in error messages displayed by Microsoft Access 97. [purple][b]uMsg[/b][/purple] permits you to supply a message in the same three sections for the Message argument. [blue]You separate the sections with the [purple][b]@[/b][/purple] character.[/blue]

The following example displays a formatted message box with a [blue]sectioned message[/blue]. The [blue]first section[/blue] of text in the message is displayed [blue]as a bold heading[/blue]. The [blue]second section[/blue] is displayed as [blue]plain text beneath that heading[/blue]. The [blue]third section[/blue] is [blue]displayed as plain text beneath the second section[/blue], with a blank line between them. Example:

[blue]uMsg("Wrong button!@This button doesn't work.@Try another.") displays as:

[b]Wrong button![/b]

This button doesn't work.

Try another.[/blue]

I think you get the Idea . . . Now the code.

In a module in the Modules Window, in the Declarations Section, copy/paste the follwoing code:

[blue]Public Msg As String, Style As Integer, Title As String
Public Const NL As String = vbNewLine [green]'New Line[/green]
Public Const DL As String = NL & NL   [green]'Double Line[/green]
Public Const DQ As String = """" [green]     'Used by EVal[/green][/blue]

The declarations make the variables [blue]global[/blue]. So you can [purple]use them anywhere with out declaration[/purple], as you'll see.

Now, in the same module, copy/paste the [purple]uMsg[/purple] function below:

[blue]Public Function uMsg() As Integer
   Beep
   uMsg = Eval("MsgBox(" & DQ & Msg & DQ & "," & Style & "," & DQ & Title & DQ & ")")
End Function[/blue]

Compile & save the code.

You use [purple]uMsg[/purple] in the exact same way as [blue]MsgBox[/blue]. All you have to do is assign Msg, Style, and Title. NL & DL are for custom formatting.

Thats it. Replace messaging in your code (at the top) with the following to see the results:

[blue]   Style = vbInformation + vbOKCancel
   Title = "A Required Selection is Blank!"

   ' Check for missing required data
   If IsNull(Me!txtInstr) Then
      Msg = "Please enter an Instructor Name Abbreviation." & DL & _
            "Example:  EINSTEIN A A" & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If umsg = vbCancel Then
         Exit Sub
      End If
   ElseIf IsNull(Me!txtLName) Then
      Msg = "Please enter the Instructor Last Name." & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If umsg = vbCancel Then
         Exit Sub
      End If
   ElseIf IsNull(Me!txtFName) Then
      Msg = "Please enter the Instructor First Name." & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If umsg = vbCancel Then
         Exit Sub
      End If
   ElseIf IsNull(Me!cboCategory) Then
      Msg = "Please select the Instructor Category." & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If umsg = vbCancel Then
         Exit Sub
      End If
   Else[/blue]

In the messaging above, when user selects [blue]Cancel[/blue], are you closing the form . . . . what happens when you close a session?

[purple]Have a goodnight Amadea . . . . .[/purple]

See Ya! . . . . . .

 

[Amadea]

Wow, very nice. I think I had Exit Sub when the user selects Cancel, but I've changed that so the user is positioned at the combo box. I'm assuming that if the user chooses to cancel the add event, they will probably want to choose an existing record to edit. If not, then they can click the close button to close the form and select another form from the main selection screen. So, I deleted Exit Sub and added an DoCmd.GoToControl .

There's still one thing I don't like. After a new record is added, that new record is not available to display even though it is listed and can be selected in the combo box. Probably because I deleted the DoCmd.ShowAllRecords ??? Is there any way around that? Otherwise, the user must close the screen and reopen it to view or edit the new record(s). That seems wrong to me.

Here's what I have now:

Private Sub cmdAddInstr_Click()
On Error GoTo Err_cmdAddInstr_Click

    Style = vbInformation + vbOKCancel
    Title = "A Required Selection is Blank!"

' Check for missing required data
   If IsNull(Me!txtInstr) Then
      Msg = "Please enter an Instructor Name Abbreviation." & DL & _
            "Example:  EINSTEIN A A" & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If uMsg = vbCancel Then
         DoCmd.GoToControl "cboInstrName"
      End If
   ElseIf IsNull(Me!txtLName) Then
      Msg = "Please enter the Instructor Last Name." & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If uMsg = vbCancel Then
         DoCmd.GoToControl "cboInstrName"
      End If
   ElseIf IsNull(Me!txtFName) Then
      Msg = "Please enter the Instructor First Name." & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If uMsg = vbCancel Then
         DoCmd.GoToControl "cboInstrName"
      End If
   ElseIf IsNull(Me!cboCategory) Then
      Msg = "Please select the Instructor Category." & _
            "@Press 'OK' to return and enter text." & _
            "@Press 'Cancel' to close session."
      If uMsg = vbCancel Then
         DoCmd.GoToControl "cboInstrName"
      End If
   Else
    Dim strSQL As String
    Dim strSQL2 As String
    
    strSQL = "INSERT INTO InstrName (DivNum, EmpID, Instr, LName, FName, MI, Category, Status, Step)" & _
                "VALUES ('" & DivNum & "', ' ', '" & txtInstr & "', '" & txtLName & "', '" & txtFName & "', '" & txtMI & "', '" & cboCategory & "', 'ACTIVE', ' ');"
                   
    strSQL2 = "INSERT INTO OfficeHours (DivNum, Instr, Office, Phone, Ext1, Ext2, Hours, Print)" & _
                "VALUES ('" & DivNum & "', '" & txtInstr & "', '', '', '', '', '', 'Yes');"

' Evaluate: full time instr is added to both tables; part time instr is added only to InstrName table
    If Me!cboCategory = "FT" Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL)
        DoCmd.RunSQL (strSQL2)
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Requery "cboInstrName"
        DoCmd.GoToControl "cboInstrName"
        
        MsgBox ("Add Successful!" _
            & Chr(13) & Chr(10) & Chr(10) _
        & "Geez... you're good." _
            & Chr(13) & Chr(10) _
        & "Check the drop down list " _
            & Chr(13) & Chr(10) _
        & "for your added instructor name.")
        
    Else
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL)
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Requery "cboInstrName"
        DoCmd.GoToControl "cboInstrName"
        
        MsgBox ("Add Successful!" _
            & Chr(13) & Chr(10) & Chr(10) _
        & "Geez... you're good." _
            & Chr(13) & Chr(10) _
        & "Check the drop down list " _
            & Chr(13) & Chr(10) _
        & "for your added instructor name.")
    End If
    
    ' Reset the ADD Instructor fields to blank content
    Me!txtInstr.Value = Null
    Me!txtLName.Value = Null
    Me!txtFName.Value = Null
    Me!txtMI.Value = Null
    Me!cboCategory.Value = Null
    
' Hide the fields for adding a new instructor - User must click an add button to redisplay these fields
    Dim ctl As Control
       
    For Each ctl In Me.Controls
        If ctl.Tag = "Hide" Then ctl.Visible = False
    Next ctl
End If
    
Exit_cmdAddInstr_Click:
    Exit Sub

Err_cmdAddInstr_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddInstr_Click
    
End Sub

I've moved my old message about the add succeeding within the branching. Is that legal? I'm going to try to reformat to the uMsg style (got all my fingers and toes crossed).

A thousand thank yous for your expertise.
Amadea

 

[TheAceMan1]

[blue]Earlier on you said:
[purple]I omitted the docmd.showallrecords[/purple] because I failed to realize that it removed the filter I have set for the form..[/blue]

Then you said:

[blue]After a new record is added, [purple]that new record is not available to display[/purple] even though it is listed and can be selected in the combo box.[/blue]

Instead of [purple]docmd.showallrecords[/purple] use [purple]Me.Requery[/purple] to requery the form. The filter is maintained, just be aware, the new record has to pass the filter criteria. If there's a chance the new record might not be included according to criteria, then use the [purple]docmd.showallrecords[/purple] instead.

Now . . . . your messaging is still not quite up to par. Consider you have the following options in a message:

[blue]Press 'OK' to return and enter text.
Press 'Cancel' to close session.[/blue]

You've taken care of selecting [blue]'Cancel'[/blue], but what if the user select [blue]'OK'[/blue]? Not only is [blue]'OK'[/blue] out of context with the prompt, but if selected, the routine simply goes on and adds the record/s. Think about what you really want here and [blue]make it so![/blue].

[blue]I've moved my old message about the add succeeding within the branching. Is that legal?[/blue]

Well . . . . if you look at it, you'll realize its displayed wether [blue]Me!cboCategory = "FT"[/blue] or not. So it does'nt matter if its displayed before/after the [blue]If[/blue] statement! Here it is formatted for [purple]Umsg[/purple]:

[blue]      Msg = "Add Successful!" & DL & _
            "Geez... you're good." & _
            "@Check the drop down list " & _
            "@for your added instructor name."
      Style = vbInformation + vbOKOnly
      Title = "Records Added!"
      Call uMsg[/blue]

See Ya! . . . . . .

 

[Amadea]

You're the best! I see exactly what you mean. I'm not really thinking everything thoroughly through in setting up the messaging. I'll pay closer attention in the future.

Also, the Me.Requery is perfect! Exactly what I was looking for, but obviously didn't know how to look for it.

Thanks bunches of stars!!
Amadea