Ambiguous Error for NotInList code

Hi all! :-) Windows XP Pro - Access 2002 XP

The code below is in the NotInList event of a combo box on a data entry
form.

*****Begin Code*********
Private Sub cmbTransaction_NotInList(NewData As String, Response As Integer)
Sub cmbTransaction_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransaction As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Record") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("cmbTransaction") = NewData
.Update
.Bookmark = .LastModified
lngTransaction = .Fields("cmbTransaction")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmAddTransaction", _
wherecondition:="cmbTransction=" & lngTransaction, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If
***********************End Code******************

The purpose of the two is so that when a transaction is found not to be in
the list during normal data entry processing, a message will appear and say
it is not in the list, would you like to add a new record. When the user
clicks yes, then a separate from is opened to allow the new data to be added
to the table. When the new data is saved, the second form will close and
return to the data entry form so that the user can continue with other data
entry. This will allow the user to enter new data much faster and easier
when it comes up during the normal entry process. I have a similar set up
in another form for adding new vendors and it works perfectly. However, I
am getting an error message when I click the command button to open the
second entry form to add the new data to the table, and the debugger also
points to the second line of code from the top above.

The error message says:

The expression On Click you entered as the event property setting produced
the following error: Ambiguous name detected: cmbTransaction_NotInList.

*The expression may result in the name of a macro, the name of a user...etc.

I have gone over the code until I'm blind and just can't find where the
error might be. There is no other name like that in the database, so I don't
know why it is barking at that part of the code.

I would truly appreciate if someone could point me in the right direction of
where to look to troubleshoot further, or perhaps where the code is not
correct. I may just be overlooking something there.

Jan
Smiles are meant to be shared,
that's why they're so contagious.

"Jan Il" wrote in message
...
Hi all! :-) Windows XP Pro - Access 2002 XP

The code below is in the NotInList event of a combo box on a data
entry
form.

*****Begin Code*********
Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer)
Sub cmbTransaction_NotInList(NewData As String, Response As Integer)
[...]
However, I
am getting an error message when I click the command button to open
the
second entry form to add the new data to the table, and the debugger
also
points to the second line of code from the top above.

The error message says:

The expression On Click you entered as the event property setting
produced
the following error: Ambiguous name detected:
cmbTransaction_NotInList.

*The expression may result in the name of a macro, the name of a
user...etc.

I have gone over the code until I'm blind and just can't find where
the
error might be. There is no other name like that in the database, so I
don't
know why it is barking at that part of the code.

Hi, Jan!

The error is actually right there in the code you posted: you have two
header lines for the same procedure. One says:

Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer)

and the other says:

Sub cmbTransaction_NotInList(NewData As String, Response As Integer)

Delete the second one, and also look to see if maybe you have two End
Sub lines at the end of it all, where you should only have one.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote in message
...
"Jan Il" wrote in message
...
Hi all! :-) Windows XP Pro - Access 2002 XP

The code below is in the NotInList event of a combo box on a data
entry
form.

*****Begin Code*********
Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer)
Sub cmbTransaction_NotInList(NewData As String, Response As Integer)
[...]
However, I
am getting an error message when I click the command button to open
the
second entry form to add the new data to the table, and the debugger
also
points to the second line of code from the top above.

The error message says:

The expression On Click you entered as the event property setting
produced
the following error: Ambiguous name detected:
cmbTransaction_NotInList.

*The expression may result in the name of a macro, the name of a
user...etc.

I have gone over the code until I'm blind and just can't find where
the
error might be. There is no other name like that in the database, so I
don't
know why it is barking at that part of the code.

Hi, Jan!

Hi Dirk! :-)

The error is actually right there in the code you posted: you have two
header lines for the same procedure. One says:

Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer)

and the other says:

Sub cmbTransaction_NotInList(NewData As String, Response As Integer)

Delete the second one, and also look to see if maybe you have two End
Sub lines at the end of it all, where you should only have one.

Oh... Lord have mercy!! I have been working on this nearly round the clock
for three days, and back and forth with another piece of code for another
combo box, and I guess it got added in and just didn't register that it was
out of whack. :/

I've deleted that line, and now things there are no longer complaining, and
there aren't any extra End subs, at least nothing is showing up at this
time.

What I am getting is that now when I enter the check number into the
txtCheckNo field and hit the tab to go to the date field, I am getting an
error and it jumps to the debugger with the error "Compile error, sub or
function not defined", and points to the part of the code here If .Value =
"DBT" _ , and then when I click ok, it highlights the top line of the code
in yellow.

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub

When I delete the code below that's in the command button to open the second
form, then I can enter the check number and tab to the next field without
error. Here is the code in the command button:

Private Sub cmdAddTransaction_Click()
On Error GoTo Err_cmdAddTransaction_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewTransaction"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddTransaction_Click:
Exit Sub

Err_cmdAddTransaction_Click:
MsgBox Err.Description
Resume Exit_cmdAddTransaction_Click

End Sub

I have checked the names of the controls and forms and all is correct. Is
there something I've overlooked that would make the debugger pick on the
DBT code in that text box?

Thank you very much for your time and help, I really appreciate it. :-)

Jan

"Jan Il" wrote in message

Oh... Lord have mercy!! I have been working on this nearly round the
clock for three days, and back and forth with another piece of code
for another combo box, and I guess it got added in and just didn't
register that it was out of whack. :/

It happens to us all.

I've deleted that line, and now things there are no longer
complaining, and there aren't any extra End subs, at least nothing is
showing up at this time.

What I am getting is that now when I enter the check number into the
txtCheckNo field and hit the tab to go to the date field, I am
getting an error and it jumps to the debugger with the error "Compile
error, sub or function not defined", and points to the part of the
code here If .Value = "DBT" _ , and then when I click ok, it
highlights the top line of the code in yellow.

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub

When I delete the code below that's in the command button to open the
second form, then I can enter the check number and tab to the next
field without error. Here is the code in the command button:

Private Sub cmdAddTransaction_Click()
On Error GoTo Err_cmdAddTransaction_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewTransaction"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddTransaction_Click:
Exit Sub

Err_cmdAddTransaction_Click:
MsgBox Err.Description
Resume Exit_cmdAddTransaction_Click

End Sub

I have checked the names of the controls and forms and all is
correct. Is there something I've overlooked that would make the
debugger pick on the DBT code in that text box?

Nothing is popping out at me. I take it txtCheckNo is a text box on the
form. Is it the form bound form, and is txtCheckNo bound to a field in
the form's RecordSource? If not, I don't think it will have an
..OldValue property.

Assuming that's not the cause of the problem, I wonder if there's
something messed up in the form's module outside of that particular
procedure. Would you be willing to post the entire contents of the
form's module? Set the module window to "Full Module View" so that you
can copy everything, even bits of code that aren't in any procedure.

Unfortunately, I'm heading off to bed now (an early night for me), so I
won't be able to look at your reply until tomorrow. But maybe someone
else will spot the problem in the mean time.

Good night.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk :-)

"Jan Il" wrote in message

Oh... Lord have mercy!! I have been working on this nearly round the
clock for three days, and back and forth with another piece of code
for another combo box, and I guess it got added in and just didn't
register that it was out of whack. :/

It happens to us all.

I've deleted that line, and now things there are no longer
complaining, and there aren't any extra End subs, at least nothing is
showing up at this time.

What I am getting is that now when I enter the check number into the
txtCheckNo field and hit the tab to go to the date field, I am
getting an error and it jumps to the debugger with the error "Compile
error, sub or function not defined", and points to the part of the
code here If .Value = "DBT" _ , and then when I click ok, it
highlights the top line of the code in yellow.

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub

When I delete the code below that's in the command button to open the
second form, then I can enter the check number and tab to the next
field without error. Here is the code in the command button:

Private Sub cmdAddTransaction_Click()
On Error GoTo Err_cmdAddTransaction_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewTransaction"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddTransaction_Click:
Exit Sub

Err_cmdAddTransaction_Click:
MsgBox Err.Description
Resume Exit_cmdAddTransaction_Click

End Sub

I have checked the names of the controls and forms and all is
correct. Is there something I've overlooked that would make the
debugger pick on the DBT code in that text box?

Nothing is popping out at me. I take it txtCheckNo is a text box on the
form. Is it the form bound form, and is txtCheckNo bound to a field in
the form's RecordSource? If not, I don't think it will have an
.OldValue property.

Assuming that's not the cause of the problem, I wonder if there's
something messed up in the form's module outside of that particular
procedure. Would you be willing to post the entire contents of the
form's module? Set the module window to "Full Module View" so that you
can copy everything, even bits of code that aren't in any procedure.

Unfortunately, I'm heading off to bed now (an early night for me), so I
won't be able to look at your reply until tomorrow. But maybe someone
else will spot the problem in the mean time.

Good night.

Heh...I crashed right after I posted my last reply! I woke this morning
with keyboard indentations in my forehead! g So, here's all the code in
that forms module:
*****************************************
Option Compare Database
Option Explicit
Private Sub cmbTransaction_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransaction As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Record") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("cmbTransaction") = NewData
.Update
.Bookmark = .LastModified
lngTransaction = .Fields("cmbTransaction")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmNewTransaction", _
wherecondition:="cmbTransction=" & lngTransaction, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If

Exit_He
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub
Private Sub cmdCancelRec_Click()
Dim Response As Variant

Response = MsgBox("Are your sure you want to cancel?", vbYesNo,
"Confirmmation Required!")
If Response = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Exit Sub
End If

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

DoCmd.GoToRecord , , acNewRec
Me![txtCheckNo].SetFocus

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub
Private Sub cmdAddTransaction_Click()
On Error GoTo Err_cmdAddTransaction_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewTransaction"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddTransaction_Click:
Exit Sub

Err_cmdAddTransaction_Click:
MsgBox Err.Description
Resume Exit_cmdAddTransaction_Click

End Sub

Private Sub Command51_Click()
On Error GoTo Err_Command51_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command51_Click:
Exit Sub

Err_Command51_Click:
MsgBox Err.Description
Resume Exit_Command51_Click

End Sub

Private Sub txtTransactionType_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransactionType As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Record") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("txtTransactionType") = NewData
.Update
.Bookmark = .LastModified
lngTransactionType = .Fields("txtTransactionType")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmAddTransaction", _
wherecondition:="txtTransctionType=" & lngTransactionType, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If

Exit_He
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub
*********************************
Everything works fine now until I add the code to the cmdAddTransaction
command button. Then it fires the error message.

Jan

"Jan Il" wrote in message


Heh...I crashed right after I posted my last reply! I woke this
morning with keyboard indentations in my forehead! g So, here's
all the code in that forms module:
*****************************************
Option Compare Database
Option Explicit
Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer) On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransaction As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Record") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("cmbTransaction") = NewData
.Update
.Bookmark = .LastModified
lngTransaction = .Fields("cmbTransaction")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmNewTransaction", _
wherecondition:="cmbTransction=" & lngTransaction, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If

Exit_He
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub
Private Sub cmdCancelRec_Click()
Dim Response As Variant

Response = MsgBox("Are your sure you want to cancel?", vbYesNo,
"Confirmmation Required!")
If Response = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Exit Sub
End If

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.GoToRecord , , acNewRec
Me![txtCheckNo].SetFocus

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub
Private Sub cmdAddTransaction_Click()
On Error GoTo Err_cmdAddTransaction_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewTransaction"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddTransaction_Click:
Exit Sub

Err_cmdAddTransaction_Click:
MsgBox Err.Description
Resume Exit_cmdAddTransaction_Click

End Sub

Private Sub Command51_Click()
On Error GoTo Err_Command51_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command51_Click:
Exit Sub

Err_Command51_Click:
MsgBox Err.Description
Resume Exit_Command51_Click

End Sub

Private Sub txtTransactionType_NotInList(NewData As String, Response
As Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransactionType As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Record") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("txtTransactionType") = NewData
.Update
.Bookmark = .LastModified
lngTransactionType = .Fields("txtTransactionType")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmAddTransaction", _
wherecondition:="txtTransctionType=" & lngTransactionType, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If

Exit_He
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub
*********************************
Everything works fine now until I add the code to the
cmdAddTransaction command button. Then it fires the error message.

Jan - I'm not sure what cmdAddTransaction has to do with it, but I get
the sort of result you describe if the function NextDBTNumber is not
defined in either the form's module or a standard module. When the
error dialog is displayed for you -- before you click OK on it -- is
that function name highlighted (shown as selected) by any chance? If
so, is that function defined in a standard module? If it's defined in
some other form's module, it's not going to be seen by this one.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk :-)

"Jan Il" wrote in message


Heh...I crashed right after I posted my last reply! I woke this
morning with keyboard indentations in my forehead! g So, here's
all the code in that forms module:
*****************************************
Option Compare Database
Option Explicit
Private Sub cmbTransaction_NotInList(NewData As String, Response As
Integer) On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransaction As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Record") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("cmbTransaction") = NewData
.Update
.Bookmark = .LastModified
lngTransaction = .Fields("cmbTransaction")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmNewTransaction", _
wherecondition:="cmbTransction=" & lngTransaction, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If

Exit_He
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub

Private Sub txtCheckNo_AfterUpdate()

With Me!txtCheckNo
If .Value = "DBT" _
And IsNull(.OldValue) _
Then
.Value = NextDBTNumber()
End If
End With

End Sub
Private Sub cmdCancelRec_Click()
Dim Response As Variant

Response = MsgBox("Are your sure you want to cancel?", vbYesNo,
"Confirmmation Required!")
If Response = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Exit Sub
End If

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.GoToRecord , , acNewRec
Me![txtCheckNo].SetFocus

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub
Private Sub cmdAddTransaction_Click()
On Error GoTo Err_cmdAddTransaction_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewTransaction"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddTransaction_Click:
Exit Sub

Err_cmdAddTransaction_Click:
MsgBox Err.Description
Resume Exit_cmdAddTransaction_Click

End Sub

Private Sub Command51_Click()
On Error GoTo Err_Command51_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command51_Click:
Exit Sub

Err_Command51_Click:
MsgBox Err.Description
Resume Exit_Command51_Click

End Sub

Private Sub txtTransactionType_NotInList(NewData As String, Response
As Integer)
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim lngTransactionType As Long

If MsgBox(NewData & " ... not in list, add it?", _
vbOKCancel, "New Record") = vbOK Then
Set rst = CurrentDb.OpenRecordset("MyCheckRegister")
With rst
.AddNew
.Fields("txtTransactionType") = NewData
.Update
.Bookmark = .LastModified
lngTransactionType = .Fields("txtTransactionType")
.Close
End With
Response = acDataErrAdded
DoCmd.OpenForm FormName:="frmAddTransaction", _
wherecondition:="txtTransctionType=" & lngTransactionType, _
WindowMode:=acDialog
Else
Response = acDataErrContinue
End If

Exit_He
Set rst = Nothing
Exit Sub
Err_Handler:
Response = acDataErrContinue
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub
*********************************
Everything works fine now until I add the code to the
cmdAddTransaction command button. Then it fires the error message.

Jan - I'm not sure what cmdAddTransaction has to do with it, but I get
the sort of result you describe if the function NextDBTNumber is not
defined in either the form's module or a standard module. When the
error dialog is displayed for you -- before you click OK on it -- is
that function name highlighted (shown as selected) by any chance? If
so, is that function defined in a standard module? If it's defined in
some other form's module, it's not going to be seen by this one.

Aha!! Yes...*this* goes in there! I overlooked adding this in when I redid
the form. This is code you helped me with last year.....finally getting to
put it into full use.... g:

Private Function NextDBTNumber() As String

' This function finds the highest "DBT" check number currently on
' file and adds 1 to it to get a new DBT number.

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax("CheckNo", "MyCheckRegister", _
"CheckNo Like 'DBT*'")

If Len(strMaxNum) = 0 Then
NextDBTNumber = "DBT000001"
Else
NextDBTNumber = _
"DBT" & Format(1 + CLng(Mid(strMaxNum, 4)), "000000")
End If

End Function

After adding the above code, the fields now work as they should, no errors.
When I tab from the fields there are not error messages. When a new
transaction needs to be entered, I can click on the Add Transaction command
button and it opens the Add form fine and all goes well. The only one thing
is that, if a new entry is started in the data entry form, with the check
number and date already entered, and it is found when you get to the
Transaction field that a transaction is not in the list, which will require
the new transaction be added, when I click the command button to open the
Add form, the data already entered on the data entry form is somehow being
saved. Thus, when I enter the check number and date into the Add form, it
can not be saved when I click to Save Record, as the error message says it
will create a duplicate record.

What I *think* may be needed is code, either in the command button that
opens the other Add form, or the data entry form module, that will clear any
unsaved entries in the data entry form when the Add form is opened, to avoid
any data already entered in the first form from being saved, thus, setting
up a duplicate entry. However, I am not sure exactly where the code should
be entered. In the command button to activate at the time it is clicked, or
perhaps in one of the form events that will activate when the Add form is
opened. However, perhaps that would not be the best method to resolve the
issue. Do you have any suggestions or thoughts on such a procedure?

Jan

"Jan Il" wrote in message


Aha!! Yes...*this* goes in there! I overlooked adding this in when
I redid the form. This is code you helped me with last
year.....finally getting to put it into full use.... g:

I thought the name and general "problem space" looked familiar.

The only one thing is that, if a new entry is started in
the data entry form, with the check number and date already entered,
and it is found when you get to the Transaction field that a
transaction is not in the list, which will require the new
transaction be added, when I click the command button to open the Add
form, the data already entered on the data entry form is somehow
being saved. Thus, when I enter the check number and date into the
Add form, it can not be saved when I click to Save Record, as the
error message says it will create a duplicate record.

What I *think* may be needed is code, either in the command button
that opens the other Add form, or the data entry form module, that
will clear any unsaved entries in the data entry form when the Add
form is opened, to avoid any data already entered in the first form
from being saved, thus, setting up a duplicate entry. However, I am
not sure exactly where the code should be entered. In the command
button to activate at the time it is clicked, or perhaps in one of
the form events that will activate when the Add form is opened.
However, perhaps that would not be the best method to resolve the
issue. Do you have any suggestions or thoughts on such a procedure?

I don't see anything in the code you posted that would be forcing an
early save of the current record. Is there a subform involved here
somewhere? If not, my best guess is that there's logic on some other
form that is inserting a record that matches this one. However, it may
be that there's something odd about your data structure and the way your
forms are bound to the various tables. What are the tables and the
relationships among them? What are the names of the forms involved, and
what tables or queries are these forms bound to?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote in message
...
"Jan Il" wrote in message


Aha!! Yes...*this* goes in there! I overlooked adding this in when
I redid the form. This is code you helped me with last
year.....finally getting to put it into full use.... g:

I thought the name and general "problem space" looked familiar.

Thank goodness one of us still has a memory chip working! I keep trying to
find an update to download, but, it appears the mfg. no longer makes
replacement parts. ;o))

The only one thing is that, if a new entry is started in
the data entry form, with the check number and date already entered,
and it is found when you get to the Transaction field that a
transaction is not in the list, which will require the new
transaction be added, when I click the command button to open the Add
form, the data already entered on the data entry form is somehow
being saved. Thus, when I enter the check number and date into the
Add form, it can not be saved when I click to Save Record, as the
error message says it will create a duplicate record.

What I *think* may be needed is code, either in the command button
that opens the other Add form, or the data entry form module, that
will clear any unsaved entries in the data entry form when the Add
form is opened, to avoid any data already entered in the first form
from being saved, thus, setting up a duplicate entry. However, I am
not sure exactly where the code should be entered. In the command
button to activate at the time it is clicked, or perhaps in one of
the form events that will activate when the Add form is opened.
However, perhaps that would not be the best method to resolve the
issue. Do you have any suggestions or thoughts on such a procedure?

I don't see anything in the code you posted that would be forcing an
early save of the current record. Is there a subform involved here
somewhere? If not, my best guess is that there's logic on some other
form that is inserting a record that matches this one. However, it may
be that there's something odd about your data structure and the way your
forms are bound to the various tables. What are the tables and the
relationships among them? What are the names of the forms involved, and
what tables or queries are these forms bound to?

'k...there is only the one small table, and now two forms for the checking
part of the db.

Here is the infromation you requested:

Table:
MyCheckRegister.

Queries:
qryCkRegisterDan
qryCkExpenseType
qryTransactionType

Forms:
frmCkEntry
the new one is frmNewTransactions

There is nothing else that I have been able to find that has any
relationship to this part of the db that I am aware of that could influence
this save. I could just click the Cancel button, but, there is the chance
that the user might forget to click the Cancel before clicking the Add
command button and then trying to update the data, only to find it won't
save.

Jan

"Jan Il" wrote in message

"Dirk Goldgar" wrote in message
...
"Jan Il" wrote in message


Aha!! Yes...*this* goes in there! I overlooked adding this in when
I redid the form. This is code you helped me with last
year.....finally getting to put it into full use.... g:

I thought the name and general "problem space" looked familiar.

Thank goodness one of us still has a memory chip working! I keep
trying to find an update to download, but, it appears the mfg. no
longer makes replacement parts. ;o))

The only one thing is that, if a new entry is started in
the data entry form, with the check number and date already entered,
and it is found when you get to the Transaction field that a
transaction is not in the list, which will require the new
transaction be added, when I click the command button to open the
Add form, the data already entered on the data entry form is somehow
being saved. Thus, when I enter the check number and date into the
Add form, it can not be saved when I click to Save Record, as the
error message says it will create a duplicate record.

What I *think* may be needed is code, either in the command button
that opens the other Add form, or the data entry form module, that
will clear any unsaved entries in the data entry form when the Add
form is opened, to avoid any data already entered in the first form
from being saved, thus, setting up a duplicate entry. However, I am
not sure exactly where the code should be entered. In the command
button to activate at the time it is clicked, or perhaps in one of
the form events that will activate when the Add form is opened.
However, perhaps that would not be the best method to resolve the
issue. Do you have any suggestions or thoughts on such a procedure?

I don't see anything in the code you posted that would be forcing an
early save of the current record. Is there a subform involved here
somewhere? If not, my best guess is that there's logic on some other
form that is inserting a record that matches this one. However, it
may be that there's something odd about your data structure and the
way your forms are bound to the various tables. What are the tables
and the relationships among them? What are the names of the forms
involved, and what tables or queries are these forms bound to?

'k...there is only the one small table, and now two forms for the
checking part of the db.

Here is the infromation you requested:

Table:
MyCheckRegister.

Queries:
qryCkRegisterDan
qryCkExpenseType
qryTransactionType

Forms:
frmCkEntry
the new one is frmNewTransactions

There is nothing else that I have been able to find that has any
relationship to this part of the db that I am aware of that could
influence this save. I could just click the Cancel button, but, there
is the chance that the user might forget to click the Cancel before
clicking the Add command button and then trying to update the data,
only to find it won't save.

If there's only one table, what is frmNewTransactions supposed to do
that frmCkEntry doesn't do?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)