Not in list, add record.

C

Chris

Morning. I hope I can impose again...as I am still learning even through my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box to pull
up their info onto the main client form. The client is coded with an ID of 3
alpha (always the first three of last name) with numeric following that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one to
populate the client form or determines that a particular client does not yet
exist in the list. Now they wish to add that client and complete the client
form and some other housework.

Presently I am using a convoluted three form process to determine need to
add, then add, then open a new client entry form (not the main client form)
to enter data. Cumbersome, but it works. There has to be a better way, but I
cannot get it right.

I want my user to enter the first three letters of new client's last name,
determine they don't exist, and automatically increment the client ID by one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to allow for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this move to
simplicity for my user.

Thanks in advance,
Chris
 
G

grep

Chris,

I know someone will have a better answer than this, but what you're
trying to accomplish, ultimately, would be *so* much easier if you just
switched to a client number and used autonumber. Then you wouldn't have
to calculate the new clientID at all.

grep
 
M

Marshall Barton

Chris said:
Morning. I hope I can impose again...as I am still learning even through my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box to pull
up their info onto the main client form. The client is coded with an ID of 3
alpha (always the first three of last name) with numeric following that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one to
populate the client form or determines that a particular client does not yet
exist in the list. Now they wish to add that client and complete the client
form and some other housework.

Presently I am using a convoluted three form process to determine need to
add, then add, then open a new client entry form (not the main client form)
to enter data. Cumbersome, but it works. There has to be a better way, but I
cannot get it right.

I want my user to enter the first three letters of new client's last name,
determine they don't exist, and automatically increment the client ID by one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to allow for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]
Click to expand...


In the context of method 2 in the KB Susan referenced, you
can calculate the next number using this kind of code in the
add new client form:

ClientID=Nz(DMax("CLng(Mid(ClientID,4))", "tbl 1 Client", _
"Left(ClientID,3) = """ & Me.OpenArgs & """"), 0) + 1
 
C

Chris

Yes it does! Thank you Susan. I have loaded method two and it is going to do
just fine for me when I take Marshalls suggestion into play.

I do still have an issue though. It occurs on the entry form when closing.
The record does get added, but I don't get to the new client on the main
form. Can you help?

With the following code attached to button to close the new client entry
form and return to main client form for the new client....I get a message I
have to save the
current field before running requery.

Dim NewData As String
Dim frm As Form
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new client
Set frm = Forms![frm1 Client]
frm.[cboClient].Requery
frm.Requery
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
DoCmd.Close acForm, "frm1 ClientEntry"

I can't tell what's wrong. Can you?
--
Thanks for your help,
Chris


SusanV said:
Perhaps this will help:
http://support.microsoft.com/kb/197526
--
hth,
SusanV

Chris said:
Morning. I hope I can impose again...as I am still learning even through
my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box to
pull
up their info onto the main client form. The client is coded with an ID
of 3
alpha (always the first three of last name) with numeric following that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one to
populate the client form or determines that a particular client does not
yet
exist in the list. Now they wish to add that client and complete the
client
form and some other housework.

Presently I am using a convoluted three form process to determine need to
add, then add, then open a new client entry form (not the main client
form)
to enter data. Cumbersome, but it works. There has to be a better way,
but I
cannot get it right.

I want my user to enter the first three letters of new client's last name,
determine they don't exist, and automatically increment the client ID by
one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to allow for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this move to
simplicity for my user.

Thanks in advance,
Chris
Click to expand...
Click to expand...
 
C

Chris

Thanks Marshall. I'll try that when I get the other issue resolved. Please
see reply to Susan.

Do you see anything?
--
Thanks for your help,
Chris


Marshall Barton said:
Chris said:
Morning. I hope I can impose again...as I am still learning even through my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box to pull
up their info onto the main client form. The client is coded with an ID of 3
alpha (always the first three of last name) with numeric following that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one to
populate the client form or determines that a particular client does not yet
exist in the list. Now they wish to add that client and complete the client
form and some other housework.

Presently I am using a convoluted three form process to determine need to
add, then add, then open a new client entry form (not the main client form)
to enter data. Cumbersome, but it works. There has to be a better way, but I
cannot get it right.

I want my user to enter the first three letters of new client's last name,
determine they don't exist, and automatically increment the client ID by one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to allow for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]
Click to expand...


In the context of method 2 in the KB Susan referenced, you
can calculate the next number using this kind of code in the
add new client form:

ClientID=Nz(DMax("CLng(Mid(ClientID,4))", "tbl 1 Client", _
"Left(ClientID,3) = """ & Me.OpenArgs & """"), 0) + 1
Click to expand...
 
C

Chris

Thanks for responding. I know it would but I am very entrenched in this
method.

If I had it to do all over again....
--
Thanks for your help,
Chris


grep said:
Chris,

I know someone will have a better answer than this, but what you're
trying to accomplish, ultimately, would be *so* much easier if you just
switched to a client number and used autonumber. Then you wouldn't have
to calculate the new clientID at all.

grep
Morning. I hope I can impose again...as I am still learning even through my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box to pull
up their info onto the main client form. The client is coded with an ID of 3
alpha (always the first three of last name) with numeric following that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one to
populate the client form or determines that a particular client does not yet
exist in the list. Now they wish to add that client and complete the client
form and some other housework.

Presently I am using a convoluted three form process to determine need to
add, then add, then open a new client entry form (not the main client form)
to enter data. Cumbersome, but it works. There has to be a better way, but I
cannot get it right.

I want my user to enter the first three letters of new client's last name,
determine they don't exist, and automatically increment the client ID by one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to allow for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this move to
simplicity for my user.

Thanks in advance,
Chris
Click to expand...
Click to expand...
 
S

SusanV

In the entry form's OnClose event, reload the main form:
Forms!YourMainForm.Requery

--
hth,
SusanV


Chris said:
Yes it does! Thank you Susan. I have loaded method two and it is going to
do
just fine for me when I take Marshalls suggestion into play.

I do still have an issue though. It occurs on the entry form when
closing.
The record does get added, but I don't get to the new client on the main
form. Can you help?

With the following code attached to button to close the new client entry
form and return to main client form for the new client....I get a message
I
have to save the
current field before running requery.

Dim NewData As String
Dim frm As Form
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new client
Set frm = Forms![frm1 Client]
frm.[cboClient].Requery
frm.Requery
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
DoCmd.Close acForm, "frm1 ClientEntry"

I can't tell what's wrong. Can you?
--
Thanks for your help,
Chris


SusanV said:
Perhaps this will help:
http://support.microsoft.com/kb/197526
--
hth,
SusanV

Chris said:
Morning. I hope I can impose again...as I am still learning even
through
my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box to
pull
up their info onto the main client form. The client is coded with an
ID
of 3
alpha (always the first three of last name) with numeric following
that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one to
populate the client form or determines that a particular client does
not
yet
exist in the list. Now they wish to add that client and complete the
client
form and some other housework.

Presently I am using a convoluted three form process to determine need
to
add, then add, then open a new client entry form (not the main client
form)
to enter data. Cumbersome, but it works. There has to be a better way,
but I
cannot get it right.

I want my user to enter the first three letters of new client's last
name,
determine they don't exist, and automatically increment the client ID
by
one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to allow
for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this move
to
simplicity for my user.

Thanks in advance,
Chris
Click to expand...
Click to expand...
Click to expand...
 
C

Chris

No joy!

When I remove all requeries, including the OnClose event...it closes ok and
the record is there. But I need to be on that new client's page of the new
form.

Anything else?
--
Thanks for your help,
Chris


SusanV said:
In the entry form's OnClose event, reload the main form:
Forms!YourMainForm.Requery

--
hth,
SusanV


Chris said:
Yes it does! Thank you Susan. I have loaded method two and it is going to
do
just fine for me when I take Marshalls suggestion into play.

I do still have an issue though. It occurs on the entry form when
closing.
The record does get added, but I don't get to the new client on the main
form. Can you help?

With the following code attached to button to close the new client entry
form and return to main client form for the new client....I get a message
I
have to save the
current field before running requery.

Dim NewData As String
Dim frm As Form
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new client
Set frm = Forms![frm1 Client]
frm.[cboClient].Requery
frm.Requery
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
DoCmd.Close acForm, "frm1 ClientEntry"

I can't tell what's wrong. Can you?
--
Thanks for your help,
Chris


SusanV said:
Perhaps this will help:
http://support.microsoft.com/kb/197526
--
hth,
SusanV

Morning. I hope I can impose again...as I am still learning even
through
my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box to
pull
up their info onto the main client form. The client is coded with an
ID
of 3
alpha (always the first three of last name) with numeric following
that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one to
populate the client form or determines that a particular client does
not
yet
exist in the list. Now they wish to add that client and complete the
client
form and some other housework.

Presently I am using a convoluted three form process to determine need
to
add, then add, then open a new client entry form (not the main client
form)
to enter data. Cumbersome, but it works. There has to be a better way,
but I
cannot get it right.

I want my user to enter the first three letters of new client's last
name,
determine they don't exist, and automatically increment the client ID
by
one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to allow
for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this move
to
simplicity for my user.

Thanks in advance,
Chris
Click to expand...
Click to expand...
Click to expand...
 
S

SusanV

Before closing the add item form, in the same sub, have you tried using
DoCmd.GoToRecord referencing the main form?


Chris said:
No joy!

When I remove all requeries, including the OnClose event...it closes ok
and
the record is there. But I need to be on that new client's page of the
new
form.

Anything else?
--
Thanks for your help,
Chris


SusanV said:
In the entry form's OnClose event, reload the main form:
Forms!YourMainForm.Requery

--
hth,
SusanV


Chris said:
Yes it does! Thank you Susan. I have loaded method two and it is going
to
do
just fine for me when I take Marshalls suggestion into play.

I do still have an issue though. It occurs on the entry form when
closing.
The record does get added, but I don't get to the new client on the
main
form. Can you help?

With the following code attached to button to close the new client
entry
form and return to main client form for the new client....I get a
message
I
have to save the
current field before running requery.

Dim NewData As String
Dim frm As Form
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new
client
Set frm = Forms![frm1 Client]
frm.[cboClient].Requery
frm.Requery
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
DoCmd.Close acForm, "frm1 ClientEntry"

I can't tell what's wrong. Can you?
--
Thanks for your help,
Chris


:

Perhaps this will help:
http://support.microsoft.com/kb/197526
--
hth,
SusanV

Morning. I hope I can impose again...as I am still learning even
through
my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box
to
pull
up their info onto the main client form. The client is coded with
an
ID
of 3
alpha (always the first three of last name) with numeric following
that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one
to
populate the client form or determines that a particular client does
not
yet
exist in the list. Now they wish to add that client and complete
the
client
form and some other housework.

Presently I am using a convoluted three form process to determine
need
to
add, then add, then open a new client entry form (not the main
client
form)
to enter data. Cumbersome, but it works. There has to be a better
way,
but I
cannot get it right.

I want my user to enter the first three letters of new client's last
name,
determine they don't exist, and automatically increment the client
ID
by
one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to
allow
for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this
move
to
simplicity for my user.

Thanks in advance,
Chris
Click to expand...
Click to expand...
Click to expand...
 
C

Chris

OK...looks like I'm almost there. Works like I want except the NotInList
message appears twice -- initially for the cbo lookup not in list and again
upon closing the entry form. If I disable the frm.requery, it still appears
and I don't go to correct record.

This is what I have now in the close event of the entry form.
Dim NewData As String
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new client
Dim frm As Form
Set frm = Forms![frm1 Client]
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
frm.Requery
Set frm = Nothing
DoCmd.GoToRecord acDataForm, "frm1 Client", acNewRec
DoCmd.Close acForm, "frm1 ClientEntry"

This is what I have now in the NotInList event of the cbo on the main form.
Dim Result
Dim msg As String
Dim CR As String

CR = Chr$(13)

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

' Ask the user if he or she wishes to add the new customer.
msg = "'" & NewID & "' is not in the list." & CR & CR
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Client form in data entry
' mode as a dialog form, passing the new company name in
' NewID to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Client form's Form_Load event
' procedure.
DoCmd.OpenForm "frm1 ClientEntry", , , , acAdd, acDialog, NewID
End If

' Look for the client the user created in the Client form.
Result = DLookup("[ClientID]", "[tbl 1 Client]", "[ClientID]='" & NewID
& "'")
If IsNull(Result) Then
' If the customer was not created, 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 customer was created, set the Response argument to
' indicate that new data is being added.
response = acDataErrAdded
End If

Where is the second message coming from? How do I supress it?
--
Thanks for your help,
Chris


SusanV said:
Before closing the add item form, in the same sub, have you tried using
DoCmd.GoToRecord referencing the main form?


Chris said:
No joy!

When I remove all requeries, including the OnClose event...it closes ok
and
the record is there. But I need to be on that new client's page of the
new
form.

Anything else?
--
Thanks for your help,
Chris


SusanV said:
In the entry form's OnClose event, reload the main form:
Forms!YourMainForm.Requery

--
hth,
SusanV


Yes it does! Thank you Susan. I have loaded method two and it is going
to
do
just fine for me when I take Marshalls suggestion into play.

I do still have an issue though. It occurs on the entry form when
closing.
The record does get added, but I don't get to the new client on the
main
form. Can you help?

With the following code attached to button to close the new client
entry
form and return to main client form for the new client....I get a
message
I
have to save the
current field before running requery.

Dim NewData As String
Dim frm As Form
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new
client
Set frm = Forms![frm1 Client]
frm.[cboClient].Requery
frm.Requery
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
DoCmd.Close acForm, "frm1 ClientEntry"

I can't tell what's wrong. Can you?
--
Thanks for your help,
Chris


:

Perhaps this will help:
http://support.microsoft.com/kb/197526
--
hth,
SusanV

Morning. I hope I can impose again...as I am still learning even
through
my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box
to
pull
up their info onto the main client form. The client is coded with
an
ID
of 3
alpha (always the first three of last name) with numeric following
that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one
to
populate the client form or determines that a particular client does
not
yet
exist in the list. Now they wish to add that client and complete
the
client
form and some other housework.

Presently I am using a convoluted three form process to determine
need
to
add, then add, then open a new client entry form (not the main
client
form)
to enter data. Cumbersome, but it works. There has to be a better
way,
but I
cannot get it right.

I want my user to enter the first three letters of new client's last
name,
determine they don't exist, and automatically increment the client
ID
by
one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to
allow
for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this
move
to
simplicity for my user.

Thanks in advance,
Chris
Click to expand...
Click to expand...
Click to expand...
 
C

Chris

Disregard my prior. I have other issues with it...but can't get a handle on
it today. I will have to revisit in several days.

I do appreciate your responses and attemt to help.
--
Thanks,
Chris


Chris said:
OK...looks like I'm almost there. Works like I want except the NotInList
message appears twice -- initially for the cbo lookup not in list and again
upon closing the entry form. If I disable the frm.requery, it still appears
and I don't go to correct record.

This is what I have now in the close event of the entry form.
Dim NewData As String
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new client
Dim frm As Form
Set frm = Forms![frm1 Client]
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
frm.Requery
Set frm = Nothing
DoCmd.GoToRecord acDataForm, "frm1 Client", acNewRec
DoCmd.Close acForm, "frm1 ClientEntry"

This is what I have now in the NotInList event of the cbo on the main form.
Dim Result
Dim msg As String
Dim CR As String

CR = Chr$(13)

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

' Ask the user if he or she wishes to add the new customer.
msg = "'" & NewID & "' is not in the list." & CR & CR
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Client form in data entry
' mode as a dialog form, passing the new company name in
' NewID to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Client form's Form_Load event
' procedure.
DoCmd.OpenForm "frm1 ClientEntry", , , , acAdd, acDialog, NewID
End If

' Look for the client the user created in the Client form.
Result = DLookup("[ClientID]", "[tbl 1 Client]", "[ClientID]='" & NewID
& "'")
If IsNull(Result) Then
' If the customer was not created, 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 customer was created, set the Response argument to
' indicate that new data is being added.
response = acDataErrAdded
End If

Where is the second message coming from? How do I supress it?
--
Thanks for your help,
Chris


SusanV said:
Before closing the add item form, in the same sub, have you tried using
DoCmd.GoToRecord referencing the main form?


Chris said:
No joy!

When I remove all requeries, including the OnClose event...it closes ok
and
the record is there. But I need to be on that new client's page of the
new
form.

Anything else?
--
Thanks for your help,
Chris


:

In the entry form's OnClose event, reload the main form:
Forms!YourMainForm.Requery

--
hth,
SusanV


Yes it does! Thank you Susan. I have loaded method two and it is going
to
do
just fine for me when I take Marshalls suggestion into play.

I do still have an issue though. It occurs on the entry form when
closing.
The record does get added, but I don't get to the new client on the
main
form. Can you help?

With the following code attached to button to close the new client
entry
form and return to main client form for the new client....I get a
message
I
have to save the
current field before running requery.

Dim NewData As String
Dim frm As Form
NewData = Me.ClientID
If Me.Dirty Then Me.Dirty = False 'closes entry form and finds new
client
Set frm = Forms![frm1 Client]
frm.[cboClient].Requery
frm.Requery
With frm.RecordsetClone
.FindFirst "ClientID=""" & Me.txtClientID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
DoCmd.Close acForm, "frm1 ClientEntry"

I can't tell what's wrong. Can you?
--
Thanks for your help,
Chris


:

Perhaps this will help:
http://support.microsoft.com/kb/197526
--
hth,
SusanV

Morning. I hope I can impose again...as I am still learning even
through
my
frustration. I am very much an amateur at this. I use not in list
frequently, but not for adding a record. I would like to do so.

My scenario: Routinely my user looks up the client via a combo box
to
pull
up their info onto the main client form. The client is coded with
an
ID
of 3
alpha (always the first three of last name) with numeric following
that.
John Doe = DOE152, Jim Smith = SMI445, etc.

The user clicks the combo box that lists the clients and selects one
to
populate the client form or determines that a particular client does
not
yet
exist in the list. Now they wish to add that client and complete
the
client
form and some other housework.

Presently I am using a convoluted three form process to determine
need
to
add, then add, then open a new client entry form (not the main
client
form)
to enter data. Cumbersome, but it works. There has to be a better
way,
but I
cannot get it right.

I want my user to enter the first three letters of new client's last
name,
determine they don't exist, and automatically increment the client
ID
by
one.
Using Jim Smith above, adding Bill Smith would incrment to SMI446.

After the record is added I will set fields on my client form to
allow
for
entry and complete the record.

Client table: [tbl 1 Client]
Key feild: [ClientID], text, indexed, no dupes.
Combo box: [cboClient]

After searching here and elsewhere, I need help to accomplish this
move
to
simplicity for my user.

Thanks in advance,
Chris
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Chris said:
Thanks Marshall. I'll try that when I get the other issue resolved. Please
see reply to Susan.

Do you see anything?
Click to expand...


Since the add new item form is called from within the combo
box's NotInList event, the new item form should not mess
with the combo box. As long as the new value was added to
the table, the combo box will be requeried automatically
according to how you set the NotInList procedure's Response
argument's value.

If you want the combo box to navigate to the new record, it
should that as part of the combo box's AfterUpdate event
procedure, which you said it already does.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2010 DB not fuctional with multiple users 2
Dynamic Naming of Form Button 3
Not in list, add record 0
Add record, return user to that record. 9
Found record not displaying when combo box requeried 5
Find prev record on other requeried form 2
Filter Combo Box Options based on other Combo Box Selection 1
After update find record on subform 5

Top