Add record, return user to that record.

[Chris]

I use my main client form's client-selector combo box NotInList event to add
a new client and to open a data entry client details form. The combo box is
updated with the new client ID.

Closing the client details form I open another data entry (greater) client
details form.

Closing the greater client details form I want to return the user to the
main client form with the newly added record displayed. The code I have
gleaned from these postings follows:

Dim frm As Form
Set frm = Forms![1frmClient]
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing


With this code I get the message asking user if they want to add the new
user (the same message from the main client form's combo box). Not surprised
-- requery.

When I remove it I am left at the main client form record at the first
record in the table, while the combo box contains the newly added client ID.
I am only able to access this new record after closing the form (or
restarting db).

Am I requerying in the wrong place? Other issue with the code?

 

[Chris]

Thanks for the reply Marshall. Here's the NotInList code.

'adds new client record
If MsgBox("Do you want to use this ID to add a new client?", vbYesNo) =
vbYes Then
NewData = UCase(NewData)
DoCmd.OpenForm "1frmDEClient", , , , acFormAdd, acDialog, NewData
response = acDataErrAdded
Else
response = acDataErrContinue
Me.cboClient = Null
End If

--
Thanks for your help,
Chrissy

I use my main client form's client-selector combo box NotInList event to add
a new client and to open a data entry client details form. The combo box is
updated with the new client ID.

Closing the client details form I open another data entry (greater) client
details form.

Closing the greater client details form I want to return the user to the
main client form with the newly added record displayed. The code I have
gleaned from these postings follows:

Dim frm As Form
Set frm = Forms![1frmClient]
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

With this code I get the message asking user if they want to add the new
user (the same message from the main client form's combo box). Not surprised
-- requery.

When I remove it I am left at the main client form record at the first
record in the table, while the combo box contains the newly added client ID.
I am only able to access this new record after closing the form (or
restarting db).

Am I requerying in the wrong place? Other issue with the code?

I can't tell from what you posted, but it sure sounds like
your NotInList procedure forgot to set the Response argumemt
to acDataErrAdded

 

[Marshall Barton]

I use my main client form's client-selector combo box NotInList event to add
a new client and to open a data entry client details form. The combo box is
updated with the new client ID.

Closing the client details form I open another data entry (greater) client
details form.

Closing the greater client details form I want to return the user to the
main client form with the newly added record displayed. The code I have
gleaned from these postings follows:

Dim frm As Form
Set frm = Forms![1frmClient]
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

With this code I get the message asking user if they want to add the new
user (the same message from the main client form's combo box). Not surprised
-- requery.

When I remove it I am left at the main client form record at the first
record in the table, while the combo box contains the newly added client ID.
I am only able to access this new record after closing the form (or
restarting db).

Am I requerying in the wrong place? Other issue with the code?

I can't tell from what you posted, but it sure sounds like
your NotInList procedure forgot to set the Response argumemt
to acDataErrAdded

 

[Klatuu]

You need to capture the Client ID before you requery.
(I wouldn't bother to establish a form reference object if the code is in
the form's module)

Dim strClient As String

strClient = Me.ClientID

With Me.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

 

[Marshall Barton]

Well, so much for my guess. That code looks good, except
for resetting Newdata (which shouldn't matter if you're only
changing case).

A very important point is that you must explicitly save the
data:
Me.Dirty = False
before requerying the main form.

If that not the problem, it could be a timing issue. You
said earlier that you close the details form when you open
the greater details form. At the point where the detail
form closes, the NotInList code resumes execution while the
greater details form is still awaiting input. I'm guessing
again, but you might want to try opening the greater details
form in dialog mode and don't close the details form until
greater details closes.
--
Marsh
MVP [MS Access]

Thanks for the reply Marshall. Here's the NotInList code.

'adds new client record
If MsgBox("Do you want to use this ID to add a new client?", vbYesNo) =
vbYes Then
NewData = UCase(NewData)
DoCmd.OpenForm "1frmDEClient", , , , acFormAdd, acDialog, NewData
response = acDataErrAdded
Else
response = acDataErrContinue
Me.cboClient = Null
End If

I use my main client form's client-selector combo box NotInList event to add
a new client and to open a data entry client details form. The combo box is
updated with the new client ID.

Closing the client details form I open another data entry (greater) client
details form.

Closing the greater client details form I want to return the user to the
main client form with the newly added record displayed. The code I have
gleaned from these postings follows:

Dim frm As Form
Set frm = Forms![1frmClient]
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

With this code I get the message asking user if they want to add the new
user (the same message from the main client form's combo box). Not surprised
-- requery.

When I remove it I am left at the main client form record at the first
record in the table, while the combo box contains the newly added client ID.
I am only able to access this new record after closing the form (or
restarting db).

Am I requerying in the wrong place? Other issue with the code?

 

[Chris]

Marshall, no joy yet!

I am simplifying though: On NotInList details form opens, via a button
greater details opens after details entered -- but details form stays open.
Upon greater details completion it is closed and user is returned to the
already open details. When it is closed (via button) the following code runs:

Dim frm As Form
Set frm = Forms![1frmClient]
Me.Dirty = False
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

DoCmd.Close

It is still giving me the NotInList message. Did I understand your response?

--
Thanks for your help,
Chrissy

Well, so much for my guess. That code looks good, except
for resetting Newdata (which shouldn't matter if you're only
changing case).

A very important point is that you must explicitly save the
data:
Me.Dirty = False
before requerying the main form.

If that not the problem, it could be a timing issue. You
said earlier that you close the details form when you open
the greater details form. At the point where the detail
form closes, the NotInList code resumes execution while the
greater details form is still awaiting input. I'm guessing
again, but you might want to try opening the greater details
form in dialog mode and don't close the details form until
greater details closes.
--
Marsh
MVP [MS Access]

Thanks for the reply Marshall. Here's the NotInList code.

'adds new client record
If MsgBox("Do you want to use this ID to add a new client?", vbYesNo) =
vbYes Then
NewData = UCase(NewData)
DoCmd.OpenForm "1frmDEClient", , , , acFormAdd, acDialog, NewData
response = acDataErrAdded
Else
response = acDataErrContinue
Me.cboClient = Null
End If

Chris wrote:
I use my main client form's client-selector combo box NotInList event to add
a new client and to open a data entry client details form. The combo box is
updated with the new client ID.

Closing the client details form I open another data entry (greater) client
details form.

Closing the greater client details form I want to return the user to the
main client form with the newly added record displayed. The code I have
gleaned from these postings follows:

Dim frm As Form
Set frm = Forms![1frmClient]
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

With this code I get the message asking user if they want to add the new
user (the same message from the main client form's combo box). Not surprised
-- requery.

When I remove it I am left at the main client form record at the first
record in the table, while the combo box contains the newly added client ID.
I am only able to access this new record after closing the form (or
restarting db).

Am I requerying in the wrong place? Other issue with the code?

 

[Chris]

Thanks for responding.

Note response to Marshall re my form flow.

Before I changed to that I attempted your code...but I am not certain where
strClientID goes. Also, isn't ClientID already captured if its used on each
bound form and does add the record?

--
Thanks for your help,
Chrissy

You need to capture the Client ID before you requery.
(I wouldn't bother to establish a form reference object if the code is in
the form's module)

Dim strClient As String

strClient = Me.ClientID

With Me.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

--
Dave Hargis, Microsoft Access MVP

I use my main client form's client-selector combo box NotInList event to add
a new client and to open a data entry client details form. The combo box is
updated with the new client ID.

Closing the client details form I open another data entry (greater) client
details form.

Closing the greater client details form I want to return the user to the
main client form with the newly added record displayed. The code I have
gleaned from these postings follows:

Dim frm As Form
Set frm = Forms![1frmClient]
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing


With this code I get the message asking user if they want to add the new
user (the same message from the main client form's combo box). Not surprised
-- requery.

When I remove it I am left at the main client form record at the first
record in the table, while the combo box contains the newly added client ID.
I am only able to access this new record after closing the form (or
restarting db).

Am I requerying in the wrong place? Other issue with the code?

 

[Marshall Barton]

Marshall, no joy yet!

I am simplifying though: On NotInList details form opens, via a button
greater details opens after details entered -- but details form stays open.
Upon greater details completion it is closed and user is returned to the
already open details. When it is closed (via button) the following code runs:

Dim frm As Form
Set frm = Forms![1frmClient]
Me.Dirty = False
frm.Requery

With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.ClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

DoCmd.Close

It is still giving me the NotInList message. Did I understand your response?

I think Dave may have hit on the issue. The NotInList
procedure should complete before the requery is done.

Try moving his code (minus the strClient lines) to the combo
box's AfterUpdate event.

 

[Chris]

Still nothing. In fact, I already have code in the after update of the combo
box for selecting clients -- it returns them nicely.

I just need to add the new record and display that new record on the form.
I'll get to the details later.

So...
How do I code an unbound client-selector combo box 's NotInList event to add
a record to a table and return the form to that new record, when that same
combo box is already used for client selection with after update code?

Combo box AfterUpdate event:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = '" & Me![cboClient] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


Chrissy

 

[Marshall Barton]

Still nothing. In fact, I already have code in the after update of the combo
box for selecting clients -- it returns them nicely.

I just need to add the new record and display that new record on the form.
I'll get to the details later.

So...
How do I code an unbound client-selector combo box 's NotInList event to add
a record to a table and return the form to that new record, when that same
combo box is already used for client selection with after update code?

Combo box AfterUpdate event:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = '" & Me![cboClient] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

From what I can see, all you need is to open the detail
form(s) in Dialog mode, create the new record(s) and when
the NotInList procedure resumes, Requery the form. Then the
combo box's AfterUpdate event will locate the new record.

One extremely important point is that the new record must
have the exact field value that was selected in the combo
box and NewData should be unchanged.

I think(?) what you posted was the long complicated way, but
generally looked like it should work. Obviously some
subtle(?) point was out of whack though. Try the
straightforward approach without all that code in the detail
forms and maybe the problem will shake itself out.