Using NotInList event for a combo box

[Guest]

I am coding a form for a database of servers. The form is bound to the
table "Server Inventory." I have a combo box (combo35) from which a server
name (ServerName - Primary Key) is selected, and the form populates the text
boxes with all appropriate information.

The problem, however, is that I would like to be able to add servers on the
fly. I researched the topic, and it seemed the best way was to code this
into a NotInList event, and came up with the following code, and modified it
for my purposes. I am not experienced enough in VBA to know exactly how to
program these commands from scratch, but I am familiar enough with object
oriented programming concepts to modify the code.

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox("Add server " & NewData & " to server inventory?", vbQuestion
+ vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

As far as I am aware, this should work. However, I am not even seeing the
message box pop up, which leads me to believe that the event isn't even
firing. Perhaps the code in the combo35_AfterUpdate method is thowing it
off? If
anyone could lend a hand, it would be much appreciated.

 

[Ken Snell [MVP]]

Is the LimitToList property of the combo box set to Yes?

 

[Guest]

It is now. Thanks.

 

[Guest]

The latest development...

So, for the most part the code works, and it definitely created a new
record. However, while the combo box is showing the new ServerName, the form
is populated with information that does not pertain to said ServerName, as if
it were pulling the wrong record from the table.

After reading some other posts, it would appear that I may need a requery of
some sort, but I am unsure as to how to implement this. Any further advice
would be appreciated.

 

[Ken Snell [MVP]]

You need to requery the recordset that uses the combo box as the "filter". I
didn't see anything in your earlier posts about what the combo box's purpose
is, so I cannot suggest a specific change. You mention the form, so perhaps
you need to requery the form? Add a Me.Requery step right after the Response
= acDataErrAdded line (see below):

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox("Add server " & NewData & " to server inventory?", vbQuestion
+ vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me.Requery
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[Guest]

You need to requery the recordset that uses the combo box as the "filter". I
didn't see anything in your earlier posts about what the combo box's purpose
is, so I cannot suggest a specific change. You mention the form, so perhaps
you need to requery the form? Add a Me.Requery step right after the Response
= acDataErrAdded line (see below):

Thanks for the response

I apologize for my previous lack of specifics, here are the cliff notes; I
am coding a form. At the top of the form is a combo box bound to the table
"Server Inventory" and the column "ServerName." Based on the ServerName
selected from the list, the AfterUpdate fires, and populates the fields with
the appropriate data from the record with the Primary Key ServerName.

I coded the NotInList event so that I could add servers "on the fly," and
wondered why it wasn't working. Setting the LimitToList property fixed that
problem, and the form now records information correctly to the table.
However, while the data makes it into the table, and the form will allow you
to edit that data, the form does not repopulate itself with the data from the
new server. (namely, all null values) It instead populates the form with
data from the alphabetically first ServerName (call it FirstServerName) found
in the combo box. When I switch from the new ServerName (call it
NewServerName) to another ServerName (call it OldServerName), the form
populates itself with the data from OldServerName. When I switch back to
NewServerName, the data still shows as the data from FirstServerName.
However, if I go into the table "Server Inventory" where all the information
resides, the data all appear as they should; even if the form originally
displayed the information from FirstServerName.

I have no idea why this would be happening. I tried the Me.Requery
function, and it trips the On Error event, but still records the data in the
table. I am hopelessly confused at this point, any help would be appreciated.

I apologize if my description of the problem is unclear, my small level of
eloquence with the English language is lost when describing technical
difficulties. I'll be happy to answer any more questions that you have to
the best of my (somewhat lacking) ability.

 

[Ken Snell [MVP]]

Comments/questions inline...

--

Ken Snell
<MS ACCESS MVP>

Thanks for the response

I apologize for my previous lack of specifics, here are the cliff notes; I
am coding a form. At the top of the form is a combo box bound to the
table
"Server Inventory" and the column "ServerName." Based on the ServerName
selected from the list, the AfterUpdate fires, and populates the fields
with
the appropriate data from the record with the Primary Key ServerName.

Are you saying that the Control Source of this combo box has the ServerName
in it? Isn't this combo box just used to "move" the form to the desired
server? If yes, it should not be bound to anything (bound means that the
ControlSource contains the name of a field in the form's RecordSource
query/table)...if it is bound, then the combo box will change the value of
ServerName field for the current record to whatever you select.

You didn't post your AfterUpdate code for the combo box, so I don't know
what it's doing specifically, but I assume that it's requerying the form's
RecordSource. If this is correct, it reinforces my comment about the combo
box's ControlSource needing to be empty.

I assume that this binding of the combo box is the source of the erroneous
data that you're seeing in the form.

I coded the NotInList event so that I could add servers "on the fly," and
wondered why it wasn't working. Setting the LimitToList property fixed
that
problem, and the form now records information correctly to the table.
However, while the data makes it into the table, and the form will allow
you
to edit that data, the form does not repopulate itself with the data from
the
new server. (namely, all null values) It instead populates the form with
data from the alphabetically first ServerName (call it FirstServerName)
found
in the combo box. When I switch from the new ServerName (call it
NewServerName) to another ServerName (call it OldServerName), the form
populates itself with the data from OldServerName. When I switch back to
NewServerName, the data still shows as the data from FirstServerName.
However, if I go into the table "Server Inventory" where all the
information
resides, the data all appear as they should; even if the form originally
displayed the information from FirstServerName.

I have no idea why this would be happening. I tried the Me.Requery
function, and it trips the On Error event, but still records the data in
the
table. I am hopelessly confused at this point, any help would be
appreciated.

What is the error that you get when you use Me.Requery? Show the code that
is using this step so that we can see how and when you're using it.

I apologize if my description of the problem is unclear, my small level of
eloquence with the English language is lost when describing technical
difficulties. I'll be happy to answer any more questions that you have to
the best of my (somewhat lacking) ability.

It's difficult to give suggestions without seeing the actual code that
you're using and understanding the use/purpose of the controls that run that
code.

 

[Guest]

The Control Source of the combo box is indeed empty. I apologize for my
incorrect usage of the term bound.

The Row Source of the combo box is SELECT [Server Inventory].[ServerName]
FROM [Server Inventory];

Here is the code that populates the form when a server name is selected from
the combo box:

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Here is the NotInList code that adds a new record into the table:

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs!CreationDate = Now
rs!LastModifiedOn = Now
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me.Requery
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

So now, when I add a new record into the table via the NotInList event, the
new record is indeed created, but VB seems to think the If Err statement is
true, so my msgBox("An error occurred, please try again.") is all I see,
there is no VB debug option. When this happens, the record is added to the
table, but not accessible via the combo box. Without the Me.Requery, the
server name appears in the combo box, but when selected, the form is not
populated with the data from the new record, namely all null values, but
instead takes the values from the record that corresponds to the first server
that appears in the combo box.

Also, on a note that may or may not be related, when the user selects 'No'
from MsgBox(NewData & " is not in the list. Would you like to add it to the
database?", vbQuestion + vbYesNo, "Add new server?") VB throws a runtime
error 91 on rs.Close

Once again, thanks for any help you can offer.

 

[Guest]

The only other code in the form is the following. This was programmed by my
predecesor, and edited by me until it worked. As far as I know, this code
works correctly, I just gave it to you in case it conflicts with anything
else.

Private Sub Form_Dirty(Cancel As Integer)
Dim username As String
Dim CurrentInt As String
If (IsNull(Me.ServerName)) Then
Exit Sub
End If

CurrentInt = Me.ServerName

If (CurrentInt = "") Then
Exit Sub
End If

username = CurrentUser
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUsername = Left$(strUserName, lngLen - 1)
Else
fOSUsername = vbNullString
End If
Me.LastChangedBy = fOSUsername
Me.LastChangedOn = Now
End Sub

 

[Ken Snell [MVP]]

Comments inline....

--

Ken Snell
<MS ACCESS MVP>

The Control Source of the combo box is indeed empty. I apologize for my
incorrect usage of the term bound.

The Row Source of the combo box is SELECT [Server Inventory].[ServerName]
FROM [Server Inventory];

Here is the code that populates the form when a server name is selected
from
the combo box:

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
Me.Bookmark = rs.Bookmark
End Sub

I would use the RecordsetClone of the form instead of the Clone of the
Recordset, and I would put in a test for NoMatch, just in case:

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

Here is the NotInList code that adds a new record into the table:

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs!CreationDate = Now
rs!LastModifiedOn = Now
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me.Requery
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

So now, when I add a new record into the table via the NotInList event,
the
new record is indeed created, but VB seems to think the If Err statement
is
true, so my msgBox("An error occurred, please try again.") is all I see,
there is no VB debug option. When this happens, the record is added to
the
table, but not accessible via the combo box. Without the Me.Requery, the
server name appears in the combo box, but when selected, the form is not
populated with the data from the new record, namely all null values, but
instead takes the values from the record that corresponds to the first
server
that appears in the combo box.

Change the Err code steps and the last steps of the procedure to this:

If Err.Number <> 0 Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me.Requery
End If

End If

On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub


What this does is test the error number. If no error exists, the number is
zero. Also, I've told the code to ignore the error (On Error Resume Next)
that will occur if rs was not set to the recordset. An alternative way of
doing this is

If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[Guest]

I had a well thought out post before, but it appears that the boards ate it,
so here we go again:

I implemented your suggested changes, but the result remained essentially
the same. Errors are still being thrown, but the data is still being
recorded to the table. The new server name appears in the combo box, but
pops the "Server not found!" message box when selected. After monkeying with
the code for a few days, I picked up that error 3265 kept popping up, as did
error number 3022.

I modified the code, hoping to just catch error 3265, and have the program
function normally. This idea did not pan out, and instead just created more
errors, this time of the 3022 variety. I assume that this is because the
data that I am trying to add into the table is already there, and VB/Access
doesn't like more than one record with the same primary key.

The code is below. Once again, many thanks.

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs!CreationDate = Now
rs!LastModifiedOn = Now
rs.Update

If Err.Number = 3265 Then
MsgBox "Error 3265 caught."
Response = acDataErrAdded
Me.Requery
ElseIf Err.Number = 3022 Then
MsgBox "Error 3022 caught."
ElseIf Err.Number <> 0 Then
MsgBox "An error occurred. Please try again. " & Err.Number
Response = acDataErrContinue
Else
Response = acDataErrAdded
Me.Requery
End If

End If

On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

 

[Ken Snell [MVP]]

Post the record source of the form that contains the combo box. Do you have
code that causes the message box "Server not found" to be displayed? or is
that an ACCESS software generated box?

 

[Guest]

Post the record source of the form that contains the combo box. Do you have

code that causes the message box "Server not found" to be displayed? or is
that an ACCESS software generated box?

The form pulls its information from the table "Server Inventory" which is
just a standard table with close to 30 columns. It looks similar to the
following;

ServerName (P.K.) -- IP Address -- Location -- ModelNumber -- SerialNumber
-- etc.

The "Server not found" error message is coded into Combo35_AfterUpdate() and
is not an access generated error message.

Thanks!

 

[Ken Snell [MVP]]

OK - you had posted that AfterUpdate code previously..my apology for
forgetting that it was there.

I do not see any obvious issue in the code that you have posted...the fact
that the form is not finding the record that you added suggests to me that
there may be a timing issue -- the requery of the form may be happening
before the table's data have been fully updated.Let's try the following
code:



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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs!CreationDate = Now
rs!LastModifiedOn = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
DoEvents
Me.Requery
If Err.Number = 3265 Then
MsgBox "Error 3265 caught."
Response = acDataErrAdded
ElseIf Err.Number = 3022 Then
MsgBox "Error 3022 caught."
ElseIf Err.Number <> 0 Then
MsgBox "An error occurred. Please try again. " & Err.Number
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

On Error Resume Next


End Sub

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub


If this still doesn't work, then my final suggestion would be to rewrite the
code to just move to a new record in the form instead of adding a new record
via the external recordset:

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
DoCmd.RunCommand acCmdRecordsGoToNew
On Error Resume Next
' add initial data for new server
With Me.RecordsetClone
.Fields("ServerName") = NewData
.Fields("CreationDate") = Now
.Fields("LastModifiedOn") = Now
End With
' save data to table
Me.Dirty = False
' requery the combo box to include new server in dropdown list
Response = acDataErrAdded
End If

On Error Resume Next


End Sub

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

 

[Guest]

Well, thank you vey much for your help. Both sets of code loop infinitely at
the "blah is not in the list, would you like to add it?" Pushing yes just
causes the same message box to spawn again, and pushing no throws all kinds
of errors. (the result is similar for both sets of code) Yet, the data is
still added to the table. It's just a little fustrating.

Looks like I'll have to think outside the box for this one, maybe a pop-up
box or subform, the combo box just isn't cutting it. Thanks!

 

[Guest]

Descriptions of the results of the source code posted below. Thanks!

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs!CreationDate = Now
rs!LastModifiedOn = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
DoEvents
Me.Requery
If Err.Number = 3265 Then
MsgBox "Error 3265 caught."
Response = acDataErrAdded
ElseIf Err.Number = 3022 Then
MsgBox "Error 3022 caught."
ElseIf Err.Number <> 0 Then
MsgBox "An error occurred. Please try again. " & Err.Number
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

On Error Resume Next


End Sub

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

When prompted to add the server to the list, the yes button doesn't function
properly, respawning the same exact prompt when pressed. Despite teh fact
that the box respawns, the data is still added to the table. The no button
pops the "Selected server not found!" error box when pushed, which appears to
be the correct behavior.

If this still doesn't work, then my final suggestion would be to rewrite the
code to just move to a new record in the form instead of adding a new record
via the external recordset:

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
DoCmd.RunCommand acCmdRecordsGoToNew
On Error Resume Next
' add initial data for new server
With Me.RecordsetClone
.Fields("ServerName") = NewData
.Fields("CreationDate") = Now
.Fields("LastModifiedOn") = Now
End With
' save data to table
Me.Dirty = False
' requery the combo box to include new server in dropdown list
Response = acDataErrAdded
End If

On Error Resume Next


End Sub

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

This set of code doesn't seem to work at all. When prompted to add the
server to the list, pushing the yes button spawns the same prompt, and the no
button crashes to the VB interface with error 2051 at "DoCmd.RunCommand
acCmdRecordsGoToNew"

Thanks for your time.

 

[Ken Snell [MVP]]

What you're doing shouldn't be "this difficult"...but short of actually
working with the database or additional information about other code that
might be in the form's module, I don't have other things to suggest for you
to try.

Just one thing that comes to mind, what is the setting for the form's
AllowAdditions property?
--

Ken Snell
<MS ACCESS MVP>

Descriptions of the results of the source code posted below. Thanks!

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it
to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Server Inventory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs!CreationDate = Now
rs!LastModifiedOn = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
DoEvents
Me.Requery
If Err.Number = 3265 Then
MsgBox "Error 3265 caught."
Response = acDataErrAdded
ElseIf Err.Number = 3022 Then
MsgBox "Error 3022 caught."
ElseIf Err.Number <> 0 Then
MsgBox "An error occurred. Please try again. " & Err.Number
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

On Error Resume Next


End Sub

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

When prompted to add the server to the list, the yes button doesn't
function
properly, respawning the same exact prompt when pressed. Despite teh fact
that the box respawns, the data is still added to the table. The no
button
pops the "Selected server not found!" error box when pushed, which appears
to
be the correct behavior.

If this still doesn't work, then my final suggestion would be to rewrite
the
code to just move to a new record in the form instead of adding a new
record
via the external recordset:

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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it
to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
DoCmd.RunCommand acCmdRecordsGoToNew
On Error Resume Next
' add initial data for new server
With Me.RecordsetClone
.Fields("ServerName") = NewData
.Fields("CreationDate") = Now
.Fields("LastModifiedOn") = Now
End With
' save data to table
Me.Dirty = False
' requery the combo box to include new server in dropdown list
Response = acDataErrAdded
End If

On Error Resume Next


End Sub

Private Sub Combo35_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[ServerName] = '" & Me![Combo35] & "'"
If rs.NoMatch = True Then
MsgBox "Selected server not found!"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

This set of code doesn't seem to work at all. When prompted to add the
server to the list, pushing the yes button spawns the same prompt, and the
no
button crashes to the VB interface with error 2051 at "DoCmd.RunCommand
acCmdRecordsGoToNew"

Thanks for your time.

 

[Guest]

Allow Additions/Deletions/Edits are all set to yes in the form's properties
window.

The Record Source is set to "Server Inventory" in the form's properties
window.

The only other code in the form are these lines in the beginning of the
program (to help snag the username in the Form_Dirty() event):

Option Compare Database
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

As well as the Form_Dirty() event that appears in the post immediately below
this one.

....and yes, everything that I;ve leared over the past few weeks points to
this as being a fairly simple task. I have to believe it's some tiny,
meddlesome setting somewhere that's making things difficult.

Thanks for all the help, even if it was in vain.

 

[Ken Snell [MVP]]

If you can zip up the database and email it to me, I'll take a look. Be sure
to send detailed instructions on how to get to the form and what to do to
cause the issue.

You can extract my email address by removing the words this is not real from
the email address posted as my "reply to" and "from" email address on this
message.

 

[Ken Snell [MVP]]

I've looked at the database. The problem is that the code is running faster
than the data update is occurring, so the form isn't seeing the new entry
right away.

Here is a better way to do what you want, using your form setup. Change the
"NotInList" event procedure to this; leave the "AfterUpdate" procedure
unchanged:


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

Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox(NewData & " is not in the list. Would you like to add it to
the database?", vbQuestion + vbYesNo, "Add new server?") = vbNo Then
Response = acDataErrContinue
Else
Set rs = Me.RecordsetClone
On Error Resume Next
rs.AddNew
rs!ServerName = NewData
rs!CreationDate = Now
rs!LastChangedOn = Now
rs.Update
Set rs = Nothing
DoEvents
Response = acDataErrAdded
End If

Err.Clear

End Sub

--

Ken Snell
<MS ACCESS MVP>