• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Form button to check primary key field and add record from unbound form.

Hi.

I am trying to have users enter information into 3 unbound fields on a form and click a button to do the following:

Check to ensure all fields are filled out.  In one is not filled out then return an error and end.

Check to ensure that the data in the MEMNAME field on the form does not appear in the MBR_NAME field of the ROSTER table.

If the name is duplicated on the table then return an error and end.  If not then add the data in all 3 fields as a new record in the ROSTER table and end.

I am getting an error when I click the button and I am unsure of how I can fix it.
Private Sub Command12_Click()

  '--- only process Save if there is data in all fields
    If IsNull(MEMNAME) Then
        MsgBox "Please enter a character name."
        MEMNAME.SetFocus
        Exit Sub
    End If
    
    If IsNull(CLASS) Then
        MsgBox "Please enter a class."
        CLASS.SetFocus
        Exit Sub
    End If
    
        If IsNull(ROLE) Then
        MsgBox "Please enter a role."
        ROLE.SetFocus
        Exit Sub
    End If
    
    Dim rst As Recordset
    '--- use the primary keys to find the record
    '--- if it is a new record, this will find no records
    Set rst = CurrentDb.OpenRecordset("select * from ROSTER where MBR_NAME=" & MEMNAME & ")
        If chkNew = True Then   '--- do we add a new record or return an error
        rst.AddNew
    Else
        
        MsgBox "Member is already on the roster."
        Exit Sub
        
    End If
    
    '--- transfer data from text boxes to table fields
    rst!MBR_NAME = MEMNAME
    rst!MBR_CLASS = CLASS
    rst!MBR_ROLE = ROLE
    rst.Update  '--- save the record
    rst.Close   '--- close the recordset
    Set rst = Nothing   '--- reclaim the memory the recordset was using

End Sub

Open in new window

0
Hobart007
Asked:
Hobart007
  • 3
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What's the error you get?

Does the validation stuff work? I normally use Nz instead of Isnull:

If Nz(Me.txClass, 0) = 0 then
  Msgbox "You must enter a class"
  Exit Sub
End If
0
 
Hobart007Author Commented:
The validation seems to work.  I will try the version you posted to see if I can get it to work and if it does so better than what I have.

The error is coming from this line (25):

Set rst = CurrentDb.OpenRecordset("select * from ROSTER where MBR_NAME=" & MEMNAME)

The error says Runtime error '3061'.  Too few parameters. Expected 1.  Please note that I changed the line to remove a syntax error
Private Sub Command12_Click()

  '--- only process Save if there is data in all fields
    If IsNull(MEMNAME) Then
        MsgBox "Please enter a character name."
        MEMNAME.SetFocus
        Exit Sub
    End If
    
    If IsNull(CLASS) Then
        MsgBox "Please enter a class."
        CLASS.SetFocus
        Exit Sub
    End If
    
        If IsNull(ROLE) Then
        MsgBox "Please enter a role."
        ROLE.SetFocus
        Exit Sub
    End If
    
    Dim rst As Recordset
    '--- use the primary keys to find the record
    '--- if it is a new record, this will find no records
    Set rst = CurrentDb.OpenRecordset("select * from ROSTER where MBR_NAME=" & MEMNAME)
        If chkNew = True Then   '--- do we add a new record or return an error
        rst.AddNew
    Else
        
        MsgBox "Member is already on the roster."
        Exit Sub
        
    End If
    
    '--- transfer data from text boxes to table fields
    rst!MBR_NAME = MEMNAME
    rst!MBR_CLASS = CLASS
    rst!MBR_ROLE = ROLE
    rst.Update  '--- save the record
    rst.Close   '--- close the recordset
    Set rst = Nothing   '--- reclaim the memory the recordset was using

End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is MBR_NAME a Text field? If so, you'll need to enclose it in single quotes:

Set rst = CurrentDb.OpenRecordset("select * from ROSTER where MBR_NAME='" & MEMNAME & "'")

Note too this error can mean that you have not properly spelled a Table or Field name, so double check your spelling.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Hobart007Author Commented:
That seems to have gotten rid of the syntax error.  Thank you!

Now my issue is that no matter what value is placed in the MEMNAME field I am getting the error code for the member already being on the ROSTER table even if they are not.  I am not sure why this is happening either.
Private Sub Command12_Click()

  '--- only process Save if there is data in all fields
    If IsNull(MEMNAME) Then
        MsgBox "Please enter a character name."
        MEMNAME.SetFocus
        Exit Sub
    End If
    
    If IsNull(CLASS) Then
        MsgBox "Please enter a class."
        CLASS.SetFocus
        Exit Sub
    End If
    
        If IsNull(ROLE) Then
        MsgBox "Please enter a role."
        ROLE.SetFocus
        Exit Sub
    End If
    
    Dim rst As Recordset
    '--- use the primary keys to find the record
    '--- if it is a new record, this will find no records
    
    Set rst = CurrentDb.OpenRecordset("select * from ROSTER where MBR_NAME='" & MEMNAME & "'")
        If chkNew = True Then   '--- do we add a new record or return an error
        rst.AddNew
    Else
        
        MsgBox "Member is already on the roster."
        Exit Sub
        
    End If
    
    '--- transfer data from text boxes to table fields
    rst!MBR_NAME = MEMNAME
    rst!MBR_CLASS = CLASS
    rst!MBR_ROLE = ROLE
    rst.Update  '--- save the record
    rst.Close   '--- close the recordset
    Set rst = Nothing   '--- reclaim the memory the recordset was using

End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your code basically refers to something (control, variable, etc) named "chkNew". If that item returns anything other than TRUE, then you get the message that the "Member is already in the roster".

It would seem that you should use the rst variable to determine if that member is in the roster. To do that, check the BOF and EOF values of that object after opening it:

Set rst = CurrentDb.OpenRecordset("select * from ROSTER where MBR_NAME='" & MEMNAME & "'")
   If (rst.EOF and rst.BOF) Then
        rst.AddNew
    Else
        MsgBox "Member is already on the roster."
        Exit Sub
    End If
0
 
Hobart007Author Commented:
Worked perfectly!  Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now