We help IT Professionals succeed at work.

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

Hobart007
Hobart007 asked
on
230 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Author

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

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

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

Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Worked perfectly!  Thank you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.