Ambiguous Error for NotInList code

[Jan Il]

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.

 

[Dirk Goldgar]

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.

 

[Jan Il]

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:

Integer)

and the other says:


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

 

[Dirk Goldgar]

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.

 

[Jan Il]

Hi Dirk

It happens to us all.


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

 

[Dirk Goldgar]

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

 

[Jan Il]

Hi Dirk

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

 

[Dirk Goldgar]

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?

 

[Jan Il]

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))

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

 

[Dirk Goldgar]

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))


'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?

 

[Jan Il]

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

The CkEntry has control settings for normal data entry that are not present
in the frmNewTransaction in order to allow new data to be entered to the
table. By using the frmNewTransaction, the user still has limited access to
the tables, and can only enter specific data. They only have two options,
Save or Cancel. The table will still control how the data can or can not be
entered from the frmNewTransaction form.

Jan

 

[Dirk Goldgar]

The CkEntry has control settings for normal data entry that are not
present in the frmNewTransaction in order to allow new data to be
entered to the table. By using the frmNewTransaction, the user still
has limited access to the tables, and can only enter specific data.
They only have two options, Save or Cancel. The table will still
control how the data can or can not be entered from the
frmNewTransaction form.

I can't quite picture how this is supposed to work. Would you be
interested in sending me a copy of the database? Empty it of anything
but test data, of course -- I have no desire to snoop into your
accounting records. If you send it, please compact it, zip it, and
e-mail me the zip file, so as to minimize the download time. You know
my e-mail address.

 

[Jan Il]

I can't quite picture how this is supposed to work. Would you be
interested in sending me a copy of the database? Empty it of anything
but test data, of course -- I have no desire to snoop into your
accounting records. If you send it, please compact it, zip it, and
e-mail me the zip file, so as to minimize the download time. You know
my e-mail address.

No worries....it's just a test app. right now. It has far more money than I
do. )

On it's way.....thank you.

Jan

 

[Dirk Goldgar]

No worries....it's just a test app. right now. It has far more money
than I do. )

I especially like the "Good Girl Bonus" from your "Fairy Godmother".

On it's way.....thank you.

Okay, I've got it and have been looking at it, but I think there are
some flaws in the logic. Follow along watchfully, in case I've gone
astray somewhere in my interpretation of what you've done and are trying
to do.

You have only one meaningful table, MyCheckRegister, and two forms based
on that table -- at least, only two that are relevant to this problem.
One of these is your form frmCkEntry, and the other is
frmNewTransaction. Both of these forms are set to open in data entry
mode, both display and allow entry to the same fields in the same table.
Both have a "Save Record" button and a "Cancel" button, although
"frmCkEntry" has an additional button to open frmNewTransaction.

There doesn't seem to me to be any reason for frmNewTransaction to
exist. Or rather, I think there would be if there were a table in your
database to model the concept of a "transaction", but there isn't. I'm
thinking that, by "transaction", you intend to represent the repeated
nature of many checkbook entries, so that you can choose a previously
entered "transaction" and not have to enter the text of it over and over
again. You could do this with or without a separate table to hold that
information -- if you had such a table, then you might need
frmNewTransaction to allow entry of a new transaction, as well as a form
to allow editing of existing transactions that were entered incorrectly.

Right now, though, you're doing this without a separate table. Instead,
your frmCkEntry uses a combo box for Transaction which has its rowsource
set to a query that returns all unique entries from the Transaction
field in MyCheckRegister. That can work, too -- but not wth combo box's
Limit To List property set to Yes, and not with the code you currently
have in the combo box's NotInList event. In a setup like this, you
would not open another form to add a new transaction; instead, you'd
decide (in the combo box's BeforeUpdate event) whether or not to accept
an entry that is not in the list, and either reject the entry by
cancelling the BeforeUpdate event, or accept it by not cancelling the
event. There'd be no need for an additional form to enter the new
transaction text, because there'd be no additional table to enter it
into.

The way you have it set up at the moment, if you begin a new entry in
frmCkEntry, then realize that this entry is for a new transaction and
click the "Add New Transaction" button, and then go ahead and enter the
same check number on frmNewTransaction along with the text describing
the new transaction, then save that record and go back to frmCkEntry and
try to complete your original entry, that record can't be saved because
a record with that check number already exists in MyCheckRegister -- you
just saved it there. So this current scheme cannot possibly work unless
you undo the record on frmCkEntry after adding the same record via
frmNewTransaction. However, I don't think you should do it that way,
because (to my mind) it's just confusing. I'd be inclined to use the
method I described in the preceding paragraph, which uses neither a
separate form nor a separate table.

 

[Jan Il]

I especially like the "Good Girl Bonus" from your "Fairy Godmother".



Okay, I've got it and have been looking at it, but I think there are
some flaws in the logic. Follow along watchfully, in case I've gone
astray somewhere in my interpretation of what you've done and are trying
to do.

You have only one meaningful table, MyCheckRegister, and two forms based
on that table -- at least, only two that are relevant to this problem.
One of these is your form frmCkEntry, and the other is
frmNewTransaction. Both of these forms are set to open in data entry
mode, both display and allow entry to the same fields in the same table.
Both have a "Save Record" button and a "Cancel" button, although
"frmCkEntry" has an additional button to open frmNewTransaction.

There doesn't seem to me to be any reason for frmNewTransaction to
exist. Or rather, I think there would be if there were a table in your
database to model the concept of a "transaction", but there isn't. I'm
thinking that, by "transaction", you intend to represent the repeated
nature of many checkbook entries, so that you can choose a previously
entered "transaction" and not have to enter the text of it over and over
again. You could do this with or without a separate table to hold that
information -- if you had such a table, then you might need
frmNewTransaction to allow entry of a new transaction, as well as a form
to allow editing of existing transactions that were entered incorrectly.

Right now, though, you're doing this without a separate table. Instead,
your frmCkEntry uses a combo box for Transaction which has its rowsource
set to a query that returns all unique entries from the Transaction
field in MyCheckRegister. That can work, too -- but not wth combo box's
Limit To List property set to Yes, and not with the code you currently
have in the combo box's NotInList event. In a setup like this, you
would not open another form to add a new transaction; instead, you'd
decide (in the combo box's BeforeUpdate event) whether or not to accept
an entry that is not in the list, and either reject the entry by
cancelling the BeforeUpdate event, or accept it by not cancelling the
event. There'd be no need for an additional form to enter the new
transaction text, because there'd be no additional table to enter it
into.

The way you have it set up at the moment, if you begin a new entry in
frmCkEntry, then realize that this entry is for a new transaction and
click the "Add New Transaction" button, and then go ahead and enter the
same check number on frmNewTransaction along with the text describing
the new transaction, then save that record and go back to frmCkEntry and
try to complete your original entry, that record can't be saved because
a record with that check number already exists in MyCheckRegister -- you
just saved it there. So this current scheme cannot possibly work unless
you undo the record on frmCkEntry after adding the same record via
frmNewTransaction. However, I don't think you should do it that way,
because (to my mind) it's just confusing. I'd be inclined to use the
method I described in the preceding paragraph, which uses neither a
separate form nor a separate table.

Yes, you're right, and I'd rather not not to use the second form, and it is
indeed only for entering new information. I have set the primary data entry
form up to do more or less as you have suggested, but, when you tell it to
accept the new Transaction data, it opens the dropdown list for you to
select one from the list instead, not allow it to be accepted. I have not
been able to work around that. What it should do is just what you said,
when the message pops up and says it is an item not in the list, and you say
ok, and then that you want to allow the new data to be entered, it should
allow the user to continue the entry and save it. But, it does not. I was
testing with the second idea to see if it would work better. But, as you
can see, it poses yet another problem.

I'll go back to the drawing board and see if I can get the code ironed out
in the first data entry form so that it will accept the new data, yet, I
still want it to limit normal data entry to the items in the list. I want to
avoid having any user direct access to the table, thus, I was trying to set
up a way for the new data to be entered via the data entry process, limiting
it to just the information required on the form, instead of having to open
the table and enter it there. Plus, it would simplify the flow of the data
entry process. We did a similar setup in the MOW database for the Expense
data entry, where a new vendor needed to be entered when the expense data
was being entered. That is somewhat the process I am try to simulate here,
but, this one does not seem to want to cooperate. :/

I guess I shouldn't play with my brain food so much. <g>

Thank you very much for all your time and help, I really do appreciate it.


Jan

 

[Dirk Goldgar]

Right now, though, you're doing this without a separate table.
Instead, your frmCkEntry uses a combo box for Transaction which has
its rowsource set to a query that returns all unique entries from
the Transaction field in MyCheckRegister. That can work, too -- but
not wth combo box's Limit To List property set to Yes, and not with
the code you currently have in the combo box's NotInList event. In
a setup like this, you would not open another form to add a new
transaction; instead, you'd decide (in the combo box's BeforeUpdate
event) whether or not to accept an entry that is not in the list,
and either reject the entry by cancelling the BeforeUpdate event, or
accept it by not cancelling the event. There'd be no need for an
additional form to enter the new transaction text, because there'd
be no additional table to enter it into.

[...]

Yes, you're right, and I'd rather not not to use the second form, and
it is indeed only for entering new information. I have set the
primary data entry form up to do more or less as you have suggested,
but, when you tell it to accept the new Transaction data, it opens
the dropdown list for you to select one from the list instead, not
allow it to be accepted. I have not been able to work around that.
What it should do is just what you said, when the message pops up and
says it is an item not in the list, and you say ok, and then that you
want to allow the new data to be entered, it should allow the user to
continue the entry and save it. But, it does not.

You need to set the combo box's Limit To List property to No, instead of
Yes. That will allow you to enter a value that is not currently in the
list.

With Limit To List set to No, the NotInList event won't fire, but you
can still detect whether the current value in the combo box is in the
list or not -- if it's not in the list, the control's ListIndex property
will have the value -1. If you want to, you could test this value in
the combo's BeforeUpdate event to force the user to confirm that the
transaction entered is not an error. You'd also want to requery the
combo box in the form's AfterUpdate event, if a new transaction was
entered. The code for these two events might look like the following:

'----- start of example code -----
Private Sub cmbTransaction_BeforeUpdate(Cancel As Integer)

With Me.cmbTransaction

If Not IsNull(.Value) Then

If .ListIndex = -1 Then

If MsgBox( _
"'" & .Value & "' is a new transaction. " & _
"Is that what you meant to type?", _
vbQuestion + vbYesNo, _
"New Transaction") _
= vbNo _
Then
Cancel = True
.Dropdown
End If

End If

End If

End With

End Sub


Private Sub Form_AfterUpdate()

With Me.cmbTransaction
If .ListIndex = -1 Then
.Requery
End If
End With

End Sub
'----- end of example code -----

 

[Jan Il]

Right now, though, you're doing this without a separate table.
Instead, your frmCkEntry uses a combo box for Transaction which has
its rowsource set to a query that returns all unique entries from
the Transaction field in MyCheckRegister. That can work, too -- but
not wth combo box's Limit To List property set to Yes, and not with
the code you currently have in the combo box's NotInList event. In
a setup like this, you would not open another form to add a new
transaction; instead, you'd decide (in the combo box's BeforeUpdate
event) whether or not to accept an entry that is not in the list,
and either reject the entry by cancelling the BeforeUpdate event, or
accept it by not cancelling the event. There'd be no need for an
additional form to enter the new transaction text, because there'd
be no additional table to enter it into.

[...]

Yes, you're right, and I'd rather not not to use the second form, and
it is indeed only for entering new information. I have set the
primary data entry form up to do more or less as you have suggested,
but, when you tell it to accept the new Transaction data, it opens
the dropdown list for you to select one from the list instead, not
allow it to be accepted. I have not been able to work around that.
What it should do is just what you said, when the message pops up and
says it is an item not in the list, and you say ok, and then that you
want to allow the new data to be entered, it should allow the user to
continue the entry and save it. But, it does not.

You need to set the combo box's Limit To List property to No, instead of
Yes. That will allow you to enter a value that is not currently in the
list.

With Limit To List set to No, the NotInList event won't fire, but you
can still detect whether the current value in the combo box is in the
list or not -- if it's not in the list, the control's ListIndex property
will have the value -1. If you want to, you could test this value in
the combo's BeforeUpdate event to force the user to confirm that the
transaction entered is not an error. You'd also want to requery the
combo box in the form's AfterUpdate event, if a new transaction was
entered. The code for these two events might look like the following:

'----- start of example code -----
Private Sub cmbTransaction_BeforeUpdate(Cancel As Integer)

With Me.cmbTransaction

If Not IsNull(.Value) Then

If .ListIndex = -1 Then

If MsgBox( _
"'" & .Value & "' is a new transaction. " & _
"Is that what you meant to type?", _
vbQuestion + vbYesNo, _
"New Transaction") _
= vbNo _
Then
Cancel = True
.Dropdown
End If

End If

End If

End With

End Sub


Private Sub Form_AfterUpdate()

With Me.cmbTransaction
If .ListIndex = -1 Then
.Requery
End If
End With

End Sub
'----- end of example code -----

Yes...that looks like it should work well. I'll give them a try and get
back to you. Thank you!

Jan

 

[Jan Il]

Right now, though, you're doing this without a separate table.
Instead, your frmCkEntry uses a combo box for Transaction which has
its rowsource set to a query that returns all unique entries from
the Transaction field in MyCheckRegister. That can work, too -- but
not wth combo box's Limit To List property set to Yes, and not with
the code you currently have in the combo box's NotInList event. In
a setup like this, you would not open another form to add a new
transaction; instead, you'd decide (in the combo box's BeforeUpdate
event) whether or not to accept an entry that is not in the list,
and either reject the entry by cancelling the BeforeUpdate event, or
accept it by not cancelling the event. There'd be no need for an
additional form to enter the new transaction text, because there'd
be no additional table to enter it into.

[...]

Yes, you're right, and I'd rather not not to use the second form, and
it is indeed only for entering new information. I have set the
primary data entry form up to do more or less as you have suggested,
but, when you tell it to accept the new Transaction data, it opens
the dropdown list for you to select one from the list instead, not
allow it to be accepted. I have not been able to work around that.
What it should do is just what you said, when the message pops up and
says it is an item not in the list, and you say ok, and then that you
want to allow the new data to be entered, it should allow the user to
continue the entry and save it. But, it does not.

You need to set the combo box's Limit To List property to No, instead of
Yes. That will allow you to enter a value that is not currently in the
list.

With Limit To List set to No, the NotInList event won't fire, but you
can still detect whether the current value in the combo box is in the
list or not -- if it's not in the list, the control's ListIndex property
will have the value -1. If you want to, you could test this value in
the combo's BeforeUpdate event to force the user to confirm that the
transaction entered is not an error. You'd also want to requery the
combo box in the form's AfterUpdate event, if a new transaction was
entered. The code for these two events might look like the following:

'----- start of example code -----
Private Sub cmbTransaction_BeforeUpdate(Cancel As Integer)

With Me.cmbTransaction

If Not IsNull(.Value) Then

If .ListIndex = -1 Then

If MsgBox( _
"'" & .Value & "' is a new transaction. " & _
"Is that what you meant to type?", _
vbQuestion + vbYesNo, _
"New Transaction") _
= vbNo _
Then
Cancel = True
.Dropdown
End If

End If

End If

End With

End Sub


Private Sub Form_AfterUpdate()

With Me.cmbTransaction
If .ListIndex = -1 Then
.Requery
End If
End With

End Sub
'----- end of example code -----

Hi Dirk!

'k... it appears to be working as it should. When I enter a new
Transaction, the message asks the question and with the Yes answer it is
allowed to accept and go to the next field. If I click no, then the
dropdown list opens, thus, a selection must be made from the list or the
entry cancelled. It also updates the table after each entry is saved.

Voila!

Merci! Merci! O Maître de code. ;-))

Jan

 

[Dirk Goldgar]

'k... it appears to be working as it should. When I enter a new
Transaction, the message asks the question and with the Yes answer it
is allowed to accept and go to the next field. If I click no, then
the dropdown list opens, thus, a selection must be made from the list
or the entry cancelled. It also updates the table after each entry
is saved.

Voila!

Very good.

Merci! Merci! O Maître de code. ;-))

I always wanted to be a Maitre D.

 

[Jan Il]

Very good.


I always wanted to be a Maitre D.

Lol!! All good things come to those who wait.....;o)

Jan