second form to edit existing info

[Guest]

Hi,

I have the following code for combo box on entry form:

Private Sub ContactNameCombo_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

And I have the following code on the form "fContactList":

Private Sub Form_Load()

Me.ContactName = Me.OpenArgs

End Sub

The problem I run into is when I need to find an existing record to update.
Ex: Contact is already in list, but changes phone number or adds email
address. Is there a way to use code to keep from typing contact's name in
both the entry form and then again in the contact list when it opens (as it
is doing now - and works great!) AND be able to find existing record when
needed for updates. I was thinking something along the lines of a message
box that asks "Do you want to update contact info?" If yes, it opens the form
to the specific contact name for updating.

Any help is appreciated!!
Thanks,
Pam

 

[ManningFan]

An easier idea is to use a listbox, and have a second editing form pop
up on the double-click event of the listbox. Then you can view all the
data easier, and edit when necessary.

Either that or add a combo box that filters the data, so that the
record associated with the Contact Name is the only record that comes
back when you choose a name from the combo box.

 

[Guest]

Thanks for the prompt reply. I'm not sure if I made what I'm trying to do
clear. As it stands now with the code shown on message, if I enter a contact
name in the combo box and it is not in my list, the contact list form opens
to a new entry with the name already filled in from the first form to prevent
retyping the name in a second form. That works great and I like that aspect,
but when I have a contact name that is in the list and they have changed
numbers or added email address, I would like a prompt asking if the entry
needs editing. If user chooses yes, it will open to that contact name. Not
sure if this can be done, but if possible, I figured I'd find the answer here.
Thanks,
Pam

 

[ManningFan]

I think I see now... It looks like your combo box assumes the person
isn't in the list. since it's opening the form with "acFormAdd". If
you want to edit a record, you're going to need to change the code
behind the combo box.

This is a bit clunky, but you can probably edit the code to look like
this:
stDocName = "fContactList"

X = DLookup("ContactID", "ContactTable", "ContactName = ' " &
ContactNameCombo_NotInList.value & " ' "
'The above should be on one line, it might be broken into 2 because of
newsgroup formatting

If IsNull(X) then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
else
stLinkCriteria = "[ContactID]=" & "'" &
Me![ContactNameCombo_NotInList] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
endif
Response = acDataErrAdded

You might need to wiggle that around a bit, but I think it'll set you
in the right direction. Essentially you're using the DLookup to see if
the person already esists, and if they don't you proceed as you used to
and if they do you open the form for editing instead of adding a new
record.

 

[ManningFan]

Actually, scratch that. Keep what you have and put this code in the
After Update event:
stLinkCriteria = "[ContactID]=" & "'" & Me![ContactNameCombo] & "'"
stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Change fContactList to:

Private Sub Form_Load()
if len(Me.OpenArgs) > 0 then
Me.ContactName = Me.OpenArgs
end if
End Sub

I need coffee...

I think I see now... It looks like your combo box assumes the person
isn't in the list. since it's opening the form with "acFormAdd". If
you want to edit a record, you're going to need to change the code
behind the combo box.

This is a bit clunky, but you can probably edit the code to look like
this:
stDocName = "fContactList"

X = DLookup("ContactID", "ContactTable", "ContactName = ' " &
ContactNameCombo_NotInList.value & " ' "
'The above should be on one line, it might be broken into 2 because of
newsgroup formatting

If IsNull(X) then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
else
stLinkCriteria = "[ContactID]=" & "'" &
Me![ContactNameCombo_NotInList] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
endif
Response = acDataErrAdded

You might need to wiggle that around a bit, but I think it'll set you
in the right direction. Essentially you're using the DLookup to see if
the person already esists, and if they don't you proceed as you used to
and if they do you open the form for editing instead of adding a new
record.

Thanks for the prompt reply. I'm not sure if I made what I'm trying to do
clear. As it stands now with the code shown on message, if I enter a contact
name in the combo box and it is not in my list, the contact list form opens
to a new entry with the name already filled in from the first form to prevent
retyping the name in a second form. That works great and I like that aspect,
but when I have a contact name that is in the list and they have changed
numbers or added email address, I would like a prompt asking if the entry
needs editing. If user chooses yes, it will open to that contact name. Not
sure if this can be done, but if possible, I figured I'd find the answer here.
Thanks,
Pam

 

[Guest]

I removed the code for the Not In List property and entered the code you gave
for After Update and changed the OnLoad code for the form fContactList. When
I enter a name that is not in the list, I get msg "The text you entered isn't
an item in list...". When I enter a name that is in list, a syntax error box
pops up with end or debug. When I select debug, it goes back to code and
highlights the DoCmd line.
I appreciate any futher help you may have.
Thanks,
Pam

Actually, scratch that. Keep what you have and put this code in the
After Update event:
stLinkCriteria = "[ContactID]=" & "'" & Me![ContactNameCombo] & "'"
stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Change fContactList to:

Private Sub Form_Load()
if len(Me.OpenArgs) > 0 then
Me.ContactName = Me.OpenArgs
end if
End Sub

I need coffee...

I think I see now... It looks like your combo box assumes the person
isn't in the list. since it's opening the form with "acFormAdd". If
you want to edit a record, you're going to need to change the code
behind the combo box.

This is a bit clunky, but you can probably edit the code to look like
this:
stDocName = "fContactList"

X = DLookup("ContactID", "ContactTable", "ContactName = ' " &
ContactNameCombo_NotInList.value & " ' "
'The above should be on one line, it might be broken into 2 because of
newsgroup formatting

If IsNull(X) then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
else
stLinkCriteria = "[ContactID]=" & "'" &
Me![ContactNameCombo_NotInList] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
endif
Response = acDataErrAdded

You might need to wiggle that around a bit, but I think it'll set you
in the right direction. Essentially you're using the DLookup to see if
the person already esists, and if they don't you proceed as you used to
and if they do you open the form for editing instead of adding a new
record.

Thanks for the prompt reply. I'm not sure if I made what I'm trying to do
clear. As it stands now with the code shown on message, if I enter a contact
name in the combo box and it is not in my list, the contact list form opens
to a new entry with the name already filled in from the first form to prevent
retyping the name in a second form. That works great and I like that aspect,
but when I have a contact name that is in the list and they have changed
numbers or added email address, I would like a prompt asking if the entry
needs editing. If user chooses yes, it will open to that contact name. Not
sure if this can be done, but if possible, I figured I'd find the answer here.
Thanks,
Pam

:

An easier idea is to use a listbox, and have a second editing form pop
up on the double-click event of the listbox. Then you can view all the
data easier, and edit when necessary.

Either that or add a combo box that filters the data, so that the
record associated with the Contact Name is the only record that comes
back when you choose a name from the combo box.

PHisaw wrote:
Hi,

I have the following code for combo box on entry form:

Private Sub ContactNameCombo_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

And I have the following code on the form "fContactList":

Private Sub Form_Load()

Me.ContactName = Me.OpenArgs

End Sub

The problem I run into is when I need to find an existing record to update.
Ex: Contact is already in list, but changes phone number or adds email
address. Is there a way to use code to keep from typing contact's name in
both the entry form and then again in the contact list when it opens (as it
is doing now - and works great!) AND be able to find existing record when
needed for updates. I was thinking something along the lines of a message
box that asks "Do you want to update contact info?" If yes, it opens the form
to the specific contact name for updating.

Any help is appreciated!!
Thanks,
Pam

 

[ManningFan]

Put everything back that was in the "NotInList" event. You can cut and
paste from your original post. This shouldn't have changed at all, I
apologize for the confusion.

In the "AfterUpdate" event, check the stLinkCriteria line. You
probably have to change it a little depending on your field names. My
guess is that you don't have a field called "ContactID". You need to
find a field you can link your combo box to (maybe you have a field
called "ContactName"?) and substitute that field name in for
"ContactID".

I removed the code for the Not In List property and entered the code you gave
for After Update and changed the OnLoad code for the form fContactList. When
I enter a name that is not in the list, I get msg "The text you entered isn't
an item in list...". When I enter a name that is in list, a syntax error box
pops up with end or debug. When I select debug, it goes back to code and
highlights the DoCmd line.
I appreciate any futher help you may have.
Thanks,
Pam

Actually, scratch that. Keep what you have and put this code in the
After Update event:
stLinkCriteria = "[ContactID]=" & "'" & Me![ContactNameCombo] & "'"
stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Change fContactList to:

Private Sub Form_Load()
if len(Me.OpenArgs) > 0 then
Me.ContactName = Me.OpenArgs
end if
End Sub

I need coffee...

I think I see now... It looks like your combo box assumes the person
isn't in the list. since it's opening the form with "acFormAdd". If
you want to edit a record, you're going to need to change the code
behind the combo box.

This is a bit clunky, but you can probably edit the code to look like
this:
stDocName = "fContactList"

X = DLookup("ContactID", "ContactTable", "ContactName = ' " &
ContactNameCombo_NotInList.value & " ' "
'The above should be on one line, it might be broken into 2 because of
newsgroup formatting

If IsNull(X) then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
else
stLinkCriteria = "[ContactID]=" & "'" &
Me![ContactNameCombo_NotInList] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
endif
Response = acDataErrAdded

You might need to wiggle that around a bit, but I think it'll set you
in the right direction. Essentially you're using the DLookup to see if
the person already esists, and if they don't you proceed as you used to
and if they do you open the form for editing instead of adding a new
record.

PHisaw wrote:
Thanks for the prompt reply. I'm not sure if I made what I'm trying to do
clear. As it stands now with the code shown on message, if I enter a contact
name in the combo box and it is not in my list, the contact list form opens
to a new entry with the name already filled in from the first form to prevent
retyping the name in a second form. That works great and I like that aspect,
but when I have a contact name that is in the list and they have changed
numbers or added email address, I would like a prompt asking if the entry
needs editing. If user chooses yes, it will open to that contact name. Not
sure if this can be done, but if possible, I figured I'd find the answer here.
Thanks,
Pam

:

An easier idea is to use a listbox, and have a second editing form pop
up on the double-click event of the listbox. Then you can view all the
data easier, and edit when necessary.

Either that or add a combo box that filters the data, so that the
record associated with the Contact Name is the only record that comes
back when you choose a name from the combo box.

PHisaw wrote:
Hi,

I have the following code for combo box on entry form:

Private Sub ContactNameCombo_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

And I have the following code on the form "fContactList":

Private Sub Form_Load()

Me.ContactName = Me.OpenArgs

End Sub

The problem I run into is when I need to find an existing record to update.
Ex: Contact is already in list, but changes phone number or adds email
address. Is there a way to use code to keep from typing contact's name in
both the entry form and then again in the contact list when it opens (as it
is doing now - and works great!) AND be able to find existing record when
needed for updates. I was thinking something along the lines of a message
box that asks "Do you want to update contact info?" If yes, it opens the form
to the specific contact name for updating.

Any help is appreciated!!
Thanks,
Pam

 

[Guest]

ManningFan,

It works perfect!! I had already changed my field name - it was ContactName,
but I failed to enter (& "'") after. It helps to enter complete line of
code. Anyway, I put a message box prompt before the open form and now it
does exactly as I had hoped. Thank you so much for your time and help!
Again, much appreciated!!
Pam

Put everything back that was in the "NotInList" event. You can cut and
paste from your original post. This shouldn't have changed at all, I
apologize for the confusion.

In the "AfterUpdate" event, check the stLinkCriteria line. You
probably have to change it a little depending on your field names. My
guess is that you don't have a field called "ContactID". You need to
find a field you can link your combo box to (maybe you have a field
called "ContactName"?) and substitute that field name in for
"ContactID".

I removed the code for the Not In List property and entered the code you gave
for After Update and changed the OnLoad code for the form fContactList. When
I enter a name that is not in the list, I get msg "The text you entered isn't
an item in list...". When I enter a name that is in list, a syntax error box
pops up with end or debug. When I select debug, it goes back to code and
highlights the DoCmd line.
I appreciate any futher help you may have.
Thanks,
Pam

Actually, scratch that. Keep what you have and put this code in the
After Update event:
stLinkCriteria = "[ContactID]=" & "'" & Me![ContactNameCombo] & "'"
stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Change fContactList to:

Private Sub Form_Load()
if len(Me.OpenArgs) > 0 then
Me.ContactName = Me.OpenArgs
end if
End Sub

I need coffee...


ManningFan wrote:
I think I see now... It looks like your combo box assumes the person
isn't in the list. since it's opening the form with "acFormAdd". If
you want to edit a record, you're going to need to change the code
behind the combo box.

This is a bit clunky, but you can probably edit the code to look like
this:
stDocName = "fContactList"

X = DLookup("ContactID", "ContactTable", "ContactName = ' " &
ContactNameCombo_NotInList.value & " ' "
'The above should be on one line, it might be broken into 2 because of
newsgroup formatting

If IsNull(X) then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
else
stLinkCriteria = "[ContactID]=" & "'" &
Me![ContactNameCombo_NotInList] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
endif
Response = acDataErrAdded

You might need to wiggle that around a bit, but I think it'll set you
in the right direction. Essentially you're using the DLookup to see if
the person already esists, and if they don't you proceed as you used to
and if they do you open the form for editing instead of adding a new
record.

PHisaw wrote:
Thanks for the prompt reply. I'm not sure if I made what I'm trying to do
clear. As it stands now with the code shown on message, if I enter a contact
name in the combo box and it is not in my list, the contact list form opens
to a new entry with the name already filled in from the first form to prevent
retyping the name in a second form. That works great and I like that aspect,
but when I have a contact name that is in the list and they have changed
numbers or added email address, I would like a prompt asking if the entry
needs editing. If user chooses yes, it will open to that contact name. Not
sure if this can be done, but if possible, I figured I'd find the answer here.
Thanks,
Pam

:

An easier idea is to use a listbox, and have a second editing form pop
up on the double-click event of the listbox. Then you can view all the
data easier, and edit when necessary.

Either that or add a combo box that filters the data, so that the
record associated with the Contact Name is the only record that comes
back when you choose a name from the combo box.

PHisaw wrote:
Hi,

I have the following code for combo box on entry form:

Private Sub ContactNameCombo_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

And I have the following code on the form "fContactList":

Private Sub Form_Load()

Me.ContactName = Me.OpenArgs

End Sub

The problem I run into is when I need to find an existing record to update.
Ex: Contact is already in list, but changes phone number or adds email
address. Is there a way to use code to keep from typing contact's name in
both the entry form and then again in the contact list when it opens (as it
is doing now - and works great!) AND be able to find existing record when
needed for updates. I was thinking something along the lines of a message
box that asks "Do you want to update contact info?" If yes, it opens the form
to the specific contact name for updating.

Any help is appreciated!!
Thanks,
Pam

 

[ManningFan]

Pay It Forward...

ManningFan,

It works perfect!! I had already changed my field name - it was ContactName,
but I failed to enter (& "'") after. It helps to enter complete line of
code. Anyway, I put a message box prompt before the open form and now it
does exactly as I had hoped. Thank you so much for your time and help!
Again, much appreciated!!
Pam

Put everything back that was in the "NotInList" event. You can cut and
paste from your original post. This shouldn't have changed at all, I
apologize for the confusion.

In the "AfterUpdate" event, check the stLinkCriteria line. You
probably have to change it a little depending on your field names. My
guess is that you don't have a field called "ContactID". You need to
find a field you can link your combo box to (maybe you have a field
called "ContactName"?) and substitute that field name in for
"ContactID".

I removed the code for the Not In List property and entered the code you gave
for After Update and changed the OnLoad code for the form fContactList. When
I enter a name that is not in the list, I get msg "The text you entered isn't
an item in list...". When I enter a name that is in list, a syntax error box
pops up with end or debug. When I select debug, it goes back to code and
highlights the DoCmd line.
I appreciate any futher help you may have.
Thanks,
Pam

:

Actually, scratch that. Keep what you have and put this code in the
After Update event:
stLinkCriteria = "[ContactID]=" & "'" & Me![ContactNameCombo] & "'"
stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Change fContactList to:

Private Sub Form_Load()
if len(Me.OpenArgs) > 0 then
Me.ContactName = Me.OpenArgs
end if
End Sub

I need coffee...


ManningFan wrote:
I think I see now... It looks like your combo box assumes the person
isn't in the list. since it's opening the form with "acFormAdd". If
you want to edit a record, you're going to need to change the code
behind the combo box.

This is a bit clunky, but you can probably edit the code to look like
this:
stDocName = "fContactList"

X = DLookup("ContactID", "ContactTable", "ContactName = ' " &
ContactNameCombo_NotInList.value & " ' "
'The above should be on one line, it might be broken into 2 because of
newsgroup formatting

If IsNull(X) then
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
else
stLinkCriteria = "[ContactID]=" & "'" &
Me![ContactNameCombo_NotInList] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
endif
Response = acDataErrAdded

You might need to wiggle that around a bit, but I think it'll set you
in the right direction. Essentially you're using the DLookup to see if
the person already esists, and if they don't you proceed as you used to
and if they do you open the form for editing instead of adding a new
record.

PHisaw wrote:
Thanks for the prompt reply. I'm not sure if I made what I'm trying to do
clear. As it stands now with the code shown on message, if I enter a contact
name in the combo box and it is not in my list, the contact list form opens
to a new entry with the name already filled in from the first form to prevent
retyping the name in a second form. That works great and I like that aspect,
but when I have a contact name that is in the list and they have changed
numbers or added email address, I would like a prompt asking if the entry
needs editing. If user chooses yes, it will open to that contact name. Not
sure if this can be done, but if possible, I figured I'd find the answer here.
Thanks,
Pam

:

An easier idea is to use a listbox, and have a second editing form pop
up on the double-click event of the listbox. Then you can view all the
data easier, and edit when necessary.

Either that or add a combo box that filters the data, so that the
record associated with the Contact Name is the only record that comes
back when you choose a name from the combo box.

PHisaw wrote:
Hi,

I have the following code for combo box on entry form:

Private Sub ContactNameCombo_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ContactNameCombo_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fContactList"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded

Exit_ContactNameCombo_NotInList:
Exit Sub

Err_ContactNameCombo_NotInList:
MsgBox Err.Description
Resume Exit_ContactNameCombo_NotInList
End Sub

And I have the following code on the form "fContactList":

Private Sub Form_Load()

Me.ContactName = Me.OpenArgs

End Sub

The problem I run into is when I need to find an existing record to update.
Ex: Contact is already in list, but changes phone number or adds email
address. Is there a way to use code to keep from typing contact's name in
both the entry form and then again in the contact list when it opens (as it
is doing now - and works great!) AND be able to find existing record when
needed for updates. I was thinking something along the lines of a message
box that asks "Do you want to update contact info?" If yes, it opens the form
to the specific contact name for updating.

Any help is appreciated!!
Thanks,
Pam