Coding Help - Please

S

Sher

The user found a problem with the code listed below. If they happen to type
in the SSN in the form where another document was already opened. Even
though I have a button "Add New Record". The problem is sometimes they are
not doing that and they type in the SSN over an existing record and when it
ask if they want to add to the current list it overrides the SSN and puts the
new number with the existing data. Is there a way when it saves to the
current list the rest of the fields become blank?


Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
 
B

Bob Hairgrove

The user found a problem with the code listed below. If they happen to type
in the SSN in the form where another document was already opened. Even
though I have a button "Add New Record". The problem is sometimes they are
not doing that and they type in the SSN over an existing record and whenit
ask if they want to add to the current list it overrides the SSN and puts the
new number with the existing data. Is there a way when it saves to the
current list the rest of the fields become blank?


Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
Click to expand...

Don't forget to add "On Error GoTo 0" here (or goto some other line
with error handling code).
End If
End Sub
Click to expand...

It would be much easier, and less error-prone, if you simply show a
message box prompting the user to click the button when a non-existing
SSN is entered. Otherwise, you could just call the Click event
procedure which you already have. Simply replace everything between
the "Else ... End If" lines with one line, e.g.:

cmdAddNewRecord_Click

Of course, you'll have to replace the "cmdAddNewRecord" with the real
name of your button. Or, if the button's OnClick event calls a
different function, use that instead.
 
K

Klatuu

I would suggest you not use a bound control as a search control. This is
what is causing your problem. You are entering an SSN that is not in the
current recordset. You say it is okay to add it, but nothing in you code is
creating a new record, so it updates it to the current record.

Make the combo an unbound control and add a text box for the SSN that is the
bound control.

Change the code to make the new SSN the current record.

Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
'Undo the Entry
Me.SSN_NotInList.Undo
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

'Here is a more efficient way to add the new record:
CurrentDb.Execute ("INSERT INTO SSN (SSN) " _
& "VALUES ('" & NewData & "');"), dbFailOnError


'Make the New Record the Current Record

With Me.RecordsetClone
.FindFirst "[SSN] = '" & NewData & "'"
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
 
S

Sher

Good Afternoon Klatuu,

I am getting errors for the following:


Me.SSN_NotInList.Undo

'Here is a more efficient way to add the new record:
CurrentDb.Execute ("INSERT INTO SSN (SSN) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Click to expand...


Klatuu said:
I would suggest you not use a bound control as a search control. This is
what is causing your problem. You are entering an SSN that is not in the
current recordset. You say it is okay to add it, but nothing in you code is
creating a new record, so it updates it to the current record.

Make the combo an unbound control and add a text box for the SSN that is the
bound control.

Change the code to make the new SSN the current record.

Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
'Undo the Entry
Me.SSN_NotInList.Undo
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

'Here is a more efficient way to add the new record:
CurrentDb.Execute ("INSERT INTO SSN (SSN) " _
& "VALUES ('" & NewData & "');"), dbFailOnError


'Make the New Record the Current Record

With Me.RecordsetClone
.FindFirst "[SSN] = '" & NewData & "'"
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub


--
Dave Hargis, Microsoft Access MVP


Sher said:
The user found a problem with the code listed below. If they happen to type
in the SSN in the form where another document was already opened. Even
though I have a button "Add New Record". The problem is sometimes they are
not doing that and they type in the SSN over an existing record and when it
ask if they want to add to the current list it overrides the SSN and puts the
new number with the existing data. Is there a way when it saves to the
current list the rest of the fields become blank?


Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
Click to expand...
Click to expand...
 
K

Klatuu

Me.SSN_NotInList.Undo
Should be
Me.SSN.Undo - In this example, SSN is the name of the combo box. My
original code was incorrect.


'Here is a more efficient way to add the new record:
CurrentDb.Execute ("INSERT INTO SSN (SSN) " _
& "VALUES ('" & NewData & "');"), dbFailOnError

INSERT INTO SSN - In this part, SSN is the name of the table to find the SSN
in.

(SSN) - In this part, SSN is the name of the field in the table SSN where
you want to put the new SSN.

If that doesn't clear it up, post back with the error number or descriptioni
and on which line the error occurs. Knowing what and where the error occurs
will help me debug it.
--
Dave Hargis, Microsoft Access MVP


Sher said:
Good Afternoon Klatuu,

I am getting errors for the following:


Me.SSN_NotInList.Undo

'Here is a more efficient way to add the new record:
CurrentDb.Execute ("INSERT INTO SSN (SSN) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Click to expand...


Klatuu said:
I would suggest you not use a bound control as a search control. This is
what is causing your problem. You are entering an SSN that is not in the
current recordset. You say it is okay to add it, but nothing in you code is
creating a new record, so it updates it to the current record.

Make the combo an unbound control and add a text box for the SSN that is the
bound control.

Change the code to make the new SSN the current record.

Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
'Undo the Entry
Me.SSN_NotInList.Undo
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

'Here is a more efficient way to add the new record:
CurrentDb.Execute ("INSERT INTO SSN (SSN) " _
& "VALUES ('" & NewData & "');"), dbFailOnError


'Make the New Record the Current Record

With Me.RecordsetClone
.FindFirst "[SSN] = '" & NewData & "'"
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub


--
Dave Hargis, Microsoft Access MVP


Sher said:
The user found a problem with the code listed below. If they happen to type
in the SSN in the form where another document was already opened. Even
though I have a button "Add New Record". The problem is sometimes they are
not doing that and they type in the SSN over an existing record and when it
ask if they want to add to the current list it overrides the SSN and puts the
new number with the existing data. Is there a way when it saves to the
current list the rest of the fields become blank?


Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Blank fields pop up for NotInList 0
NotInList not firing ? 2
Help With Code Please 5
NotInList Problem 3
Combo Box to add items to a table 4
Not in list not working 1
Not in list requery issue 4
Combo Box Adding to List--Error 91 2

Top