update form after adding to combo box

[Jess12]

Hi,

I'm trying to figure out what's missing from my code. After adding a record,
if its not already in the combo box, I need to update my form and subform to
show the new record and related records (in the subform). The form is
programmed to automatically add a client if the name is not already in the
combo box. When the new record is created, I need my form and subform to
show the new record, but it is showing information for a different record.
The master and Child relationships are set on the forms, but I need some kind
of code to make the connection between the new client that was added to the
combo box and the rest of the fields on the form. Here's my Not In List
event code:

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Dim i As Integer

Dim Msg As String

'Exit this sub if the combo box is cleared

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr

Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client...")

If i = vbYes Then

strSQL = "Insert Into [Clients] ([ClientName]) " & "values ('" &
NewData & "');"

CurrentDb.Execute strSQL, dbFailOnError

Response = acDataErrAdded

Else

Response = acDataErrContinue

End If

[Forms]![Clients Form]![CallLog subform].Requery
End Sub

 

[Carl Rapson]

Hi,

I'm trying to figure out what's missing from my code. After adding a
record,
if its not already in the combo box, I need to update my form and subform
to
show the new record and related records (in the subform). The form is
programmed to automatically add a client if the name is not already in the
combo box. When the new record is created, I need my form and subform to
show the new record, but it is showing information for a different record.
The master and Child relationships are set on the forms, but I need some
kind
of code to make the connection between the new client that was added to
the
combo box and the rest of the fields on the form. Here's my Not In List
event code:

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Dim i As Integer

Dim Msg As String

'Exit this sub if the combo box is cleared

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr

Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client...")

If i = vbYes Then

strSQL = "Insert Into [Clients] ([ClientName]) " & "values ('" &
NewData & "');"

CurrentDb.Execute strSQL, dbFailOnError

Response = acDataErrAdded

Else

Response = acDataErrContinue

End If

[Forms]![Clients Form]![CallLog subform].Requery
End Sub

Once a new record is added to the underlying table or query, you will need
to manually navigate the form to that record. You can do this with the
Bookmark property:

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Client Name]='" & NewData & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Setting the bookmark this way will fire all the normal form events (such as
Current), as well as synchronize the subform.

Carl Rapson

 

[Jess12 via AccessMonster.com]

Thanks for your response. I tried to add the code that you suggested, but I
ended up in an endless loop, probably because I already have the following
code in the After Update event.

Private Sub Combo6_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I'm wondering if the reason that it doesn't work is because the combo box
only contains the Client name and not the ID number, after the add. If this
is so, how can I get the automatically generated ID number back to the form
after the record has been added to the table.

Hi,

[quoted text clipped - 47 lines]

[Forms]![Clients Form]![CallLog subform].Requery
End Sub

Once a new record is added to the underlying table or query, you will need
to manually navigate the form to that record. You can do this with the
Bookmark property:

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Client Name]='" & NewData & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Setting the bookmark this way will fire all the normal form events (such as
Current), as well as synchronize the subform.

Carl Rapson

 

[Carl Rapson]

The code in AfterUpdate should handle things, so you don't need it in
NotInList also. Looking at your INSERT statement, I'm assuming that the ID
field is an Autonumber. The combo box should automatically requery in the
NotInList event, so having the ID number in the combo box shouldn't be a
problem.

One thing to try might be to use a different method to insert the new
record:

Dim newClientID As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Clients] WHERE False;")
rs.AddNew
newClientID = rs![ID]
rs![Client Name] = NewData
rs.Update

At this point, you'll have the newly-added ID in the variable newClientID,
which you can then use in your FindFirst.

Carl Rapson

Thanks for your response. I tried to add the code that you suggested, but
I
ended up in an endless loop, probably because I already have the following
code in the After Update event.

Private Sub Combo6_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I'm wondering if the reason that it doesn't work is because the combo box
only contains the Client name and not the ID number, after the add. If
this
is so, how can I get the automatically generated ID number back to the
form
after the record has been added to the table.

Hi,

[quoted text clipped - 47 lines]

[Forms]![Clients Form]![CallLog subform].Requery
End Sub

Once a new record is added to the underlying table or query, you will need
to manually navigate the form to that record. You can do this with the
Bookmark property:

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Client Name]='" & NewData & "'"
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Setting the bookmark this way will fire all the normal form events (such
as
Current), as well as synchronize the subform.

Carl Rapson

 

[Jess12 via AccessMonster.com]

Thanks Carl, I changed the INSERT to the way you recommended. Unfortunately,
I'm a bit of a novice, I'm not sure how to change the AfterUpdate to look for
the newClientID.

The code in AfterUpdate should handle things, so you don't need it in
NotInList also. Looking at your INSERT statement, I'm assuming that the ID
field is an Autonumber. The combo box should automatically requery in the
NotInList event, so having the ID number in the combo box shouldn't be a
problem.

One thing to try might be to use a different method to insert the new
record:

Dim newClientID As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Clients] WHERE False;")
rs.AddNew
newClientID = rs![ID]
rs![Client Name] = NewData
rs.Update

At this point, you'll have the newly-added ID in the variable newClientID,
which you can then use in your FindFirst.

Carl Rapson

Thanks for your response. I tried to add the code that you suggested, but
I

[quoted text clipped - 39 lines]

 

[Carl Rapson]

Make that variable global - declare it at the top of the module, after any
OPTION statements but before the first Sub/Function. That way, all methods
in the module have access to it. Then, in the AfterUpdate event:

rs.FindFirst "[ID] = " & newClientID

Carl Rapson

Thanks Carl, I changed the INSERT to the way you recommended.
Unfortunately,
I'm a bit of a novice, I'm not sure how to change the AfterUpdate to look
for
the newClientID.

The code in AfterUpdate should handle things, so you don't need it in
NotInList also. Looking at your INSERT statement, I'm assuming that the ID
field is an Autonumber. The combo box should automatically requery in the
NotInList event, so having the ID number in the combo box shouldn't be a
problem.

One thing to try might be to use a different method to insert the new
record:

Dim newClientID As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Clients] WHERE False;")
rs.AddNew
newClientID = rs![ID]
rs![Client Name] = NewData
rs.Update

At this point, you'll have the newly-added ID in the variable newClientID,
which you can then use in your FindFirst.

Carl Rapson

Thanks for your response. I tried to add the code that you suggested,
but
I

[quoted text clipped - 39 lines]

Carl Rapson

 

[Jess12 via AccessMonster.com]

I declared the variable as follows (I don't know if I did it correctly):

Public newClientID As Long

Also, I'm not sure where to put the rs.FindFirst "[ID]=" &newClientID
since I already have

rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))

in the AfterUpdate - I've listed the code for the AfterUpdate in a previous
post.

Make that variable global - declare it at the top of the module, after any
OPTION statements but before the first Sub/Function. That way, all methods
in the module have access to it. Then, in the AfterUpdate event:

rs.FindFirst "[ID] = " & newClientID

Carl Rapson

Thanks Carl, I changed the INSERT to the way you recommended.
Unfortunately,

[quoted text clipped - 29 lines]

 

[Carl Rapson]

Replace

rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))

with

rs.FindFirst "[ID]=" &newClientID

Carl Rapson

I declared the variable as follows (I don't know if I did it correctly):

Public newClientID As Long

Also, I'm not sure where to put the rs.FindFirst "[ID]=" &newClientID
since I already have

rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))

in the AfterUpdate - I've listed the code for the AfterUpdate in a
previous
post.

Make that variable global - declare it at the top of the module, after any
OPTION statements but before the first Sub/Function. That way, all methods
in the module have access to it. Then, in the AfterUpdate event:

rs.FindFirst "[ID] = " & newClientID

Carl Rapson

Thanks Carl, I changed the INSERT to the way you recommended.
Unfortunately,

[quoted text clipped - 29 lines]

Carl Rapson

 

[Carl Rapson]

Actually, looking back at the conversation, I think I've given you some
erroneous information. You have two processes occurring - the user selects
an item in the combo box, and the user enters a new item in the combo box.
In the first case, your existing code in the combo box's AfterUpdate event
is correct. In the second case, you need to position the form's Record
Source to the newly-entered item. To do that, you'll need to put the line

rs.FindFirst "[ID]=" &newClientID

in the NotInList event, right at the end. Note that nothing in the NotInList
event will cause the combo box's AfterUpdate event to fire.

Carl Rapson

Replace

rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))

with

rs.FindFirst "[ID]=" &newClientID

Carl Rapson

I declared the variable as follows (I don't know if I did it correctly):

Public newClientID As Long

Also, I'm not sure where to put the rs.FindFirst "[ID]=" &newClientID
since I already have

rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))

in the AfterUpdate - I've listed the code for the AfterUpdate in a
previous
post.

Make that variable global - declare it at the top of the module, after
any
OPTION statements but before the first Sub/Function. That way, all
methods
in the module have access to it. Then, in the AfterUpdate event:

rs.FindFirst "[ID] = " & newClientID

Carl Rapson

Thanks Carl, I changed the INSERT to the way you recommended.
Unfortunately,
[quoted text clipped - 29 lines]

Carl Rapson

 

[Carl Rapson]

And another thing, since you're changing the form's Record Source by adding
the new item, you may also have to requery the form before you position to
the new record.

Carl Rapson

Actually, looking back at the conversation, I think I've given you some
erroneous information. You have two processes occurring - the user selects
an item in the combo box, and the user enters a new item in the combo box.
In the first case, your existing code in the combo box's AfterUpdate event
is correct. In the second case, you need to position the form's Record
Source to the newly-entered item. To do that, you'll need to put the line

rs.FindFirst "[ID]=" &newClientID

in the NotInList event, right at the end. Note that nothing in the
NotInList event will cause the combo box's AfterUpdate event to fire.

Carl Rapson

Replace

rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))

with

rs.FindFirst "[ID]=" &newClientID

Carl Rapson

I declared the variable as follows (I don't know if I did it correctly):

Public newClientID As Long

Also, I'm not sure where to put the rs.FindFirst "[ID]=" &newClientID
since I already have

rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))

in the AfterUpdate - I've listed the code for the AfterUpdate in a
previous
post.


Carl Rapson wrote:
Make that variable global - declare it at the top of the module, after
any
OPTION statements but before the first Sub/Function. That way, all
methods
in the module have access to it. Then, in the AfterUpdate event:

rs.FindFirst "[ID] = " & newClientID

Carl Rapson

Thanks Carl, I changed the INSERT to the way you recommended.
Unfortunately,
[quoted text clipped - 29 lines]

Carl Rapson

 

[Jess12 via AccessMonster.com]

I really do appreciate all of your help, but I'm still having trouble with
this. This is what my NotInList event code looks like now.

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Dim i As Integer

Dim Msg As String

Dim newClientID As Long

'Exit this sub if the combo box is cleared

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr

Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client...")

If i = vbYes Then

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Clients] WHERE False;")
rs.AddNew
newClientID = rs![ID]
rs![ClientName] = NewData
rs.Update

CurrentDb.Execute strSQL, dbFailOnError

Response = acDataErrAdded

Else

Response = acDataErrContinue

End If

[Forms]![Clients Form].Requery

rs.FindFirst "[ID]=" & newClientID
End Sub

Do you know what's wrong with it?

Thanks.

And another thing, since you're changing the form's Record Source by adding
the new item, you may also have to requery the form before you position to
the new record.

Carl Rapson

Actually, looking back at the conversation, I think I've given you some
erroneous information. You have two processes occurring - the user selects

[quoted text clipped - 48 lines]

 

[Carl Rapson]

What's the problem you're having? Have you checked to make sure the new
record has been added to the table?

I notice that you're not resetting the recordset variable to RecordsetClone.
Try adding this just before the FindFirst call:

rs.Close
Set rs = Me.RecordsetClone

Also, I'd put the FindFirst right after

Response = acDataErrAdded

because you don't want to try to move to the record if it wasn't added.

Carl Rapson

I really do appreciate all of your help, but I'm still having trouble with
this. This is what my NotInList event code looks like now.

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Dim i As Integer

Dim Msg As String

Dim newClientID As Long

'Exit this sub if the combo box is cleared

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr

Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client...")

If i = vbYes Then

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Clients] WHERE
False;")
rs.AddNew
newClientID = rs![ID]
rs![ClientName] = NewData
rs.Update

CurrentDb.Execute strSQL, dbFailOnError

Response = acDataErrAdded

Else

Response = acDataErrContinue

End If

[Forms]![Clients Form].Requery

rs.FindFirst "[ID]=" & newClientID
End Sub

Do you know what's wrong with it?

Thanks.

And another thing, since you're changing the form's Record Source by
adding
the new item, you may also have to requery the form before you position to
the new record.

Carl Rapson

Actually, looking back at the conversation, I think I've given you some
erroneous information. You have two processes occurring - the user
selects

[quoted text clipped - 48 lines]

Carl Rapson

 

[Jess12 via AccessMonster.com]

Okay, I changed to code to the way that you recommended and now I get a runt
time error #3078 - The Jet Database engine cannot find the input table or
query. When I click on the Debug botton it highlights this line.

CurrentDb.Execute strSQL, dbFailOnError


Also, Yes, the records are being added to the tables, the form just doesn't
recognize the new record until I exit the program and go back in again.

What's the problem you're having? Have you checked to make sure the new
record has been added to the table?

I notice that you're not resetting the recordset variable to RecordsetClone.
Try adding this just before the FindFirst call:

rs.Close
Set rs = Me.RecordsetClone

Also, I'd put the FindFirst right after

Response = acDataErrAdded

because you don't want to try to move to the record if it wasn't added.

Carl Rapson

I really do appreciate all of your help, but I'm still having trouble with
this. This is what my NotInList event code looks like now.

[quoted text clipped - 59 lines]

 

[Jess12 via AccessMonster.com]

and also, now I have an endless loop where I am continually being prompted
that the record doesn't exist, do I want to add it. Every time I click the
add button it creates another record in my table, and then I see the prompt
again.

Okay, I changed to code to the way that you recommended and now I get a runt
time error #3078 - The Jet Database engine cannot find the input table or
query. When I click on the Debug botton it highlights this line.

CurrentDb.Execute strSQL, dbFailOnError

Also, Yes, the records are being added to the tables, the form just doesn't
recognize the new record until I exit the program and go back in again.

What's the problem you're having? Have you checked to make sure the new
record has been added to the table?

[quoted text clipped - 18 lines]

 

[Carl Rapson]

Can you post your NotInList event code again, with the changes? The first
problem sounds like you've mis-typed the table name in the SQL statement.

Carl Rapson

and also, now I have an endless loop where I am continually being prompted
that the record doesn't exist, do I want to add it. Every time I click
the
add button it creates another record in my table, and then I see the
prompt
again.

Okay, I changed to code to the way that you recommended and now I get a
runt
time error #3078 - The Jet Database engine cannot find the input table or
query. When I click on the Debug botton it highlights this line.

CurrentDb.Execute strSQL, dbFailOnError

Also, Yes, the records are being added to the tables, the form just
doesn't
recognize the new record until I exit the program and go back in again.

What's the problem you're having? Have you checked to make sure the new
record has been added to the table?

[quoted text clipped - 18 lines]

Carl Rapson

 

[Jess12 via AccessMonster.com]

I am posting all of the code for this form, just in case there is some kind
of conflict with one of the other events.

Option Compare Database
Public newClientID As Long
Private Sub Combo6_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Dim i As Integer

Dim Msg As String

Dim newClientID As Long

'Exit this sub if the combo box is cleared

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr

Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client...")

If i = vbYes Then

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Clients] WHERE False;")
rs.AddNew
newClientID = rs![ID]
rs![ClientName] = NewData
rs.Update

CurrentDb.Execute strSQL, dbFailOnError

Response = acDataErrAdded
rs.Close
Set rs = Me.RecordsetClone
rs.FindFirst "[ID]=" & newClientID
Else

Response = acDataErrContinue

End If

[Forms]![Clients Form].Requery

End Sub
Private Sub Form_Current()
Me.Combo6.SetFocus
End Sub

Private Sub Form_Load()
If Not Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If

End Sub

Can you post your NotInList event code again, with the changes? The first
problem sounds like you've mis-typed the table name in the SQL statement.

Carl Rapson

and also, now I have an endless loop where I am continually being prompted
that the record doesn't exist, do I want to add it. Every time I click

[quoted text clipped - 19 lines]

 

[Jess12 via AccessMonster.com]

I just realized why I'm having the run time error. I forgot to remove
CurrentDb.Execute strSQL, dbFailOnError
when I replaced it with your code.

now, I'm still having the trouble with the endless loop.

I am posting all of the code for this form, just in case there is some kind
of conflict with one of the other events.

Option Compare Database
Public newClientID As Long
Private Sub Combo6_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Dim i As Integer

Dim Msg As String

Dim newClientID As Long

'Exit this sub if the combo box is cleared

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr

Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client...")

If i = vbYes Then

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Clients] WHERE False;")
rs.AddNew
newClientID = rs![ID]
rs![ClientName] = NewData
rs.Update

CurrentDb.Execute strSQL, dbFailOnError

Response = acDataErrAdded
rs.Close
Set rs = Me.RecordsetClone
rs.FindFirst "[ID]=" & newClientID
Else

Response = acDataErrContinue

End If

[Forms]![Clients Form].Requery

End Sub
Private Sub Form_Current()
Me.Combo6.SetFocus
End Sub

Private Sub Form_Load()
If Not Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If

End Sub

Can you post your NotInList event code again, with the changes? The first
problem sounds like you've mis-typed the table name in the SQL statement.

[quoted text clipped - 6 lines]

 

[Jess12 via AccessMonster.com]

It seems as if my requery is what was causing the loop. I changed it to only
requery the subform, rather than the main form and now I'm back to my
original problem. When I enter a new client the form doesn't update the new
client information in the rest of the fields. The code to add the new client
does work, because it does appear in the table, but I can't get the form to
recognize it until I close the form and reopen it.

I just realized why I'm having the run time error. I forgot to remove
CurrentDb.Execute strSQL, dbFailOnError
when I replaced it with your code.

now, I'm still having the trouble with the endless loop.

I am posting all of the code for this form, just in case there is some kind
of conflict with one of the other events.

[quoted text clipped - 70 lines]

 

[Carl Rapson]

I've never had a problem with a requery causing an endless loop.
Unfortunately, this thread has been going on so long that I can't remember
your form design. What is the record source of the main form, and what is
the record source of the subform? When you make a selection from the combo
box or add a new entry to the combo box, which do you want to happen?

Carl Rapson

It seems as if my requery is what was causing the loop. I changed it to
only
requery the subform, rather than the main form and now I'm back to my
original problem. When I enter a new client the form doesn't update the
new
client information in the rest of the fields. The code to add the new
client
does work, because it does appear in the table, but I can't get the form
to
recognize it until I close the form and reopen it.

I just realized why I'm having the run time error. I forgot to remove
CurrentDb.Execute strSQL, dbFailOnError
when I replaced it with your code.

now, I'm still having the trouble with the endless loop.

I am posting all of the code for this form, just in case there is some
kind
of conflict with one of the other events.

[quoted text clipped - 70 lines]

Carl Rapson

 

[Jess12 via AccessMonster.com]

This form is based on 2 tables - Clients and CallLog. The mainform is based
on the Clients table and the subform on the CallLog table. The main form has
a combo box to look up clients and their respective call log information -
this part is working fine. I wanted to be able to add records to the clients
table if somebody tries to enter a client who is not already in the combo box.
The code does add the client, but once the client is added it is not
recognized by the form. I would like for the form to look up the new client
once it is added, but the client doesn't get into the combo box until I close
out the form and open it again. Is there a way to automatically add the
client and then be able to have the record on the form, with any related
information (which should only be the client name and ID from the Clients
table). Right now, what happens is, the new name appears in the combo box,
but for some reason the rest of the information that shows up on the form is
for the first client listed in the table.

I've never had a problem with a requery causing an endless loop.
Unfortunately, this thread has been going on so long that I can't remember
your form design. What is the record source of the main form, and what is
the record source of the subform? When you make a selection from the combo
box or add a new entry to the combo box, which do you want to happen?

Carl Rapson

It seems as if my requery is what was causing the loop. I changed it to
only

[quoted text clipped - 19 lines]