update form after adding to combo box

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.

Carl Rapson wrote:
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

--
Message posted via http://www.accessmonster.com

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

"Jess12 via AccessMonster.com" u36251@uwe wrote in message
news:761ade91c4331@uwe...
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.

Carl Rapson wrote:
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

--
Message posted via http://www.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.




Carl Rapson wrote:
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]

Carl Rapson

--
Message posted via http://www.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.

Jess12 wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200708/1

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

"Jess12 via AccessMonster.com" u36251@uwe wrote in message
news:761c3b9139434@uwe...
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.

Jess12 wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200708/1

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


Carl Rapson wrote:
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]

Carl Rapson

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200708/1

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.

Jess12 wrote:
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]

Carl Rapson

--
Message posted via http://www.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.

Jess12 wrote:
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

--
Message posted via http://www.accessmonster.com

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

"Jess12 via AccessMonster.com" u36251@uwe wrote in message
news:7626b734aaaef@uwe...
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.

Jess12 wrote:
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

--
Message posted via http://www.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.

Carl Rapson wrote:
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]

Carl Rapson

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200708/1