adding record to combo box

[Guest]

I am very new to Access 2007. I need to add a record to my form via a combo
box and stay on that new record. Its giving me fits. I add the record, I see
it in the combo box but am unable to select it. What I really want is after
the record is entered, the form returns to the new record, ready for data
entry.

Any help is appreciated.
Thanks.

 

[Guest]

How are you adding the new record?

 

[Guest]

I am using the not in list event. I've abandoned every attempt. I found a
few suggestions to add but what I really need is to come from a blank sheet.
Doing so, I hope to understand the process.

Thanks.

 

[Guest]

You are on the right track, I think your code needs a bit of tweeking.
If you can post the code for the Not In List, I think we can save it.

 

[Guest]

I found this at Microsoft and modified a little.

The following code adds the record fine. But I want the user to view that
record, to complete the entry process, immediately after the record is added.


How do code it to return to the new record?

Private Sub cboClient_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_ClientID_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new client.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a client, set the
Response argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new client, open a
recordset using the clients table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tbl 1 Client",
dbOpenDynaset)

' Create a new record.
Rs.AddNew
' Assign the NewID to the ClientID field.
Rs![ClientID] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new
data is being added.
Response = acDataErrAdded

End If

Exit_ClientID_NotInList:
Exit Sub
Err_ClientID_NotInList:
' An unexpected error occurred, display the normal error
message.
MsgBox Err.Description
' Set the Response argument to suppress an error message
and undo changes.
Response = acDataErrContinue

End Sub

 

[Guest]

Okay, there are only two things you need to do. First, to get the value to
be selectable in the combo box, you need to requery it. Then you need to
position the form on the new record. See code added below
--
Dave Hargis, Microsoft Access MVP

I found this at Microsoft and modified a little.

The following code adds the record fine. But I want the user to view that
record, to complete the entry process, immediately after the record is added.


How do code it to return to the new record?

Private Sub cboClient_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_ClientID_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new client.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a client, set the
Response argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new client, open a
recordset using the clients table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tbl 1 Client",
dbOpenDynaset)

' Create a new record.
Rs.AddNew
' Assign the NewID to the ClientID field.
Rs![ClientID] = NewData
' Save the record.
Rs.Update

'Update the form's recordset
Me.Requery
'Update the Combo box (I am not sure this is necessary after requerying the
form, but just in case)
Me.cboClient.Requery
'Make the New Record Current:
With Me.RecordsetClone
.FindFirst "[ClientID] = " & NewData
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If

 

[Guest]

Those changes made sense. I did change your second End If to End With.
Correct to do?

I get the message to add twice, a message that change was unsuccessful (the
second message?), and a message that I must save the current field before
requery action.

Hmmm...I thinks its so close.

Okay, there are only two things you need to do. First, to get the value to
be selectable in the combo box, you need to requery it. Then you need to
position the form on the new record. See code added below
--
Dave Hargis, Microsoft Access MVP

I found this at Microsoft and modified a little.

The following code adds the record fine. But I want the user to view that
record, to complete the entry process, immediately after the record is added.


How do code it to return to the new record?

Private Sub cboClient_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_ClientID_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new client.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a client, set the
Response argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new client, open a
recordset using the clients table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tbl 1 Client",
dbOpenDynaset)

' Create a new record.
Rs.AddNew
' Assign the NewID to the ClientID field.
Rs![ClientID] = NewData
' Save the record.
Rs.Update

'Update the form's recordset
Me.Requery
'Update the Combo box (I am not sure this is necessary after requerying the
form, but just in case)
Me.cboClient.Requery
'Make the New Record Current:
With Me.RecordsetClone
.FindFirst "[ClientID] = " & NewData
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If

' Set Response argument to indicate that new
data is being added.
Response = acDataErrAdded

End If

Exit_ClientID_NotInList:
Exit Sub
Err_ClientID_NotInList:
' An unexpected error occurred, display the normal error
message.
MsgBox Err.Description
' Set the Response argument to suppress an error message
and undo changes.
Response = acDataErrContinue

End Sub

 

[Guest]

The second End If should be End With. Sorry

I don't see the problem in the code below and I have no idea why it is
firing twice.
Have you run the code in Debug and traced it to see what is going on?
--
Dave Hargis, Microsoft Access MVP

Those changes made sense. I did change your second End If to End With.
Correct to do?

I get the message to add twice, a message that change was unsuccessful (the
second message?), and a message that I must save the current field before
requery action.

Hmmm...I thinks its so close.

Okay, there are only two things you need to do. First, to get the value to
be selectable in the combo box, you need to requery it. Then you need to
position the form on the new record. See code added below
--
Dave Hargis, Microsoft Access MVP

I found this at Microsoft and modified a little.

The following code adds the record fine. But I want the user to view that
record, to complete the entry process, immediately after the record is added.


How do code it to return to the new record?

Private Sub cboClient_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_ClientID_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new client.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a client, set the
Response argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new client, open a
recordset using the clients table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tbl 1 Client",
dbOpenDynaset)

' Create a new record.
Rs.AddNew
' Assign the NewID to the ClientID field.
Rs![ClientID] = NewData
' Save the record.
Rs.Update

'Update the form's recordset
Me.Requery
'Update the Combo box (I am not sure this is necessary after requerying the
form, but just in case)
Me.cboClient.Requery
'Make the New Record Current:
With Me.RecordsetClone
.FindFirst "[ClientID] = " & NewData
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If

' Set Response argument to indicate that new
data is being added.
Response = acDataErrAdded

End If

Exit_ClientID_NotInList:
Exit Sub
Err_ClientID_NotInList:
' An unexpected error occurred, display the normal error
message.
MsgBox Err.Description
' Set the Response argument to suppress an error message
and undo changes.
Response = acDataErrContinue

End Sub

 

[Guest]

I have run debug now. The requery is causing everything to run twice. When
I comment that statement out I get a message "must save current field before
you run the requery action." (Must be referring to the cboClient requery.

What to do?

 

[Guest]

It sounds like your combo is a bound control. It should not be. When you
change the value in the combo, you are changing the value in the current
record, which is not what you want to do.
For whatever field you now have bound to the combo, you should add a text
box and bind the field to that.

 

[Guest]

Its always been an unbound control. Its sole purpose it to retrieve the
current record. Its in the header; the client's id appears elsewhere in the
detail for user's purpose. In fact, for housekeeping I set it to null after
the record is retrieved.

We must be zeroing in on the problem.

Thanks.

 

[Guest]

hmmmmmm
somewhere in the process, a bound field control value is being changed.
That is why you are getting the error about needing to save the field. It is
trying to move away from the current record to the new record, but the
changes have not been saved.

You may try adding a line to save the current record before you do the
requery:

If Me.Dirty Then
Me.Dirty = False
End If

 

[Guest]

No joy. Same results.

I promise that the code I gave you is the entire event NotInList. What
could it be outside that event code?

Are there working alternatives? I use this cbo to pull up the current
record for the user. I know how to add using another form, like a popup, but
I really want to use my regular viewing form. Once we are getting it back
there, of course!

Thanks for the help and prompt responses. I'm very anxious to finish this
function and you are helping greatly.