After Update Event Function

I have a form that contains only a combo box (CboMoveTo).

When selecting a value on the combo box from a source table (which contains
let's say all "possible values"), it brings up another form with the
subordinate record data of the selected value.

If there is no record for the selected value, it will then prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event function to achieve the
following (when selecting a value for which NO record exists).

1. If no record exists, cache the selected value (from the combo box) and
then automatically place it into the appropriate "field" [BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter a new record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please give me some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo & """"

If rs.NoMatch Then
MsgBox "Record Not found - Add New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" & Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

You should handle the 'Not In List' scenarios by setting
the LimitToList property to Yes and coding the NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String, Response
As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box (CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event function to
achieve the
following (when selecting a value for which NO record exists).

1. If no record exists, cache the selected value (from the
combo box) and
then automatically place it into the appropriate "field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo & """"

If rs.NoMatch Then
MsgBox "Record Not found - Add New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.

Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the combo box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" - wrote in message
...
You should handle the 'Not In List' scenarios by setting
the LimitToList property to Yes and coding the NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String, Response
As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box (CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event function to
achieve the
following (when selecting a value for which NO record exists).

1. If no record exists, cache the selected value (from the
combo box) and
then automatically place it into the appropriate "field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo & """"

If rs.NoMatch Then
MsgBox "Record Not found - Add New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.

If you have coded only the NotInList and AfterUpdate
eventhandlers, then you should still be able to type into
the combo box's textbox and also see the dropdown list.
Could you confirm whether you are able to achieve either of
these actions and also post the code for the eventhandlers
back to this thread.

Gerald Stanley MCSD
-----Original Message-----
Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the combo
box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" - wrote
in message
...
You should handle the 'Not In List' scenarios by setting
the LimitToList property to Yes and coding the NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String, Response
As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box (CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event function to
achieve the
following (when selecting a value for which NO record
exists).

1. If no record exists, cache the selected value (from the
combo box) and
then automatically place it into the appropriate "field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Record Not found - Add New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.



.

Gereald:

Again, thanks for helping me out on this.

Below the &&&&s are the 2 functions. In the frmNatoBodies, I also added "=
OpenArgs" next to the event "On Load".

Again, nothing "different" really happens. If the selected value from the
combo box does not find a matching record, I'm prompted the click "Ok" for
"MsgBox "Not found: filtered?".

The selected value was NOT cached and automatically transferred into the
field "BodyName". I understand that the code you provided was not tested
(absolutely understandable w/o the application)... I appreciate if you have
any other pointers as to how I could transfer the value from the combo box
into the actual field if a matching record does not exist.

Thanks so much in advance,
Tom






&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo & """"

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If

On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" & Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

Private Sub CboMoveTo_NotInList(NewData As String, Response As Integer)

If MsgBox("Do You Wish to continue with new Value", vbYesNo) = vbYes
Then DoCmd.OpenForm "frmNatoBodies", , , , , acDialog, NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

&&&&&&&&&

--
Thanks,
Tom


"Gerald Stanley" - wrote in message
...
If you have coded only the NotInList and AfterUpdate
eventhandlers, then you should still be able to type into
the combo box's textbox and also see the dropdown list.
Could you confirm whether you are able to achieve either of
these actions and also post the code for the eventhandlers
back to this thread.

Gerald Stanley MCSD
-----Original Message-----
Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the combo
box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" - wrote
in message
...
You should handle the 'Not In List' scenarios by setting
the LimitToList property to Yes and coding the NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String, Response
As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box (CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event function to
achieve the
following (when selecting a value for which NO record
exists).

1. If no record exists, cache the selected value (from the
combo box) and
then automatically place it into the appropriate "field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Record Not found - Add New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.



.

Tom,

Try changing the DoCmd.OpenForm statement in the
AfterUpdate eventhandler to
DoCmd.OpenForm stDocName, , , stLinkCriteria, , Me![CboMoveTo]

In the Form frmNatoBodies, the Load eventhandler should
look like

Private Sub Form_Load()
[BodyName] = OpenArgs
End Sub

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gereald:

Again, thanks for helping me out on this.

Below the &&&&s are the 2 functions. In the
frmNatoBodies, I also added "=
OpenArgs" next to the event "On Load".

Again, nothing "different" really happens. If the
selected value from the
combo box does not find a matching record, I'm prompted
the click "Ok" for
"MsgBox "Not found: filtered?".

The selected value was NOT cached and automatically
transferred into the
field "BodyName". I understand that the code you
provided was not tested
(absolutely understandable w/o the application)... I
appreciate if you have
any other pointers as to how I could transfer the value
from the combo box
into the actual field if a matching record does not exist.

Thanks so much in advance,
Tom






&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo & """"

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If

On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

Private Sub CboMoveTo_NotInList(NewData As String,
Response As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes
Then DoCmd.OpenForm "frmNatoBodies", , , , , acDialog, NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

&&&&&&&&&

--
Thanks,
Tom


"Gerald Stanley" - wrote
in message
...
If you have coded only the NotInList and AfterUpdate
eventhandlers, then you should still be able to type into
the combo box's textbox and also see the dropdown list.
Could you confirm whether you are able to achieve either of
these actions and also post the code for the eventhandlers
back to this thread.

Gerald Stanley MCSD
-----Original Message-----
Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the combo
box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" - wrote
in message
...
You should handle the 'Not In List' scenarios by setting
the LimitToList property to Yes and coding the NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String,
Response
As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to
populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so
it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box
(CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it
will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event
function to
achieve the
following (when selecting a value for which NO record
exists).

1. If no record exists, cache the selected value
(from the
combo box) and
then automatically place it into the appropriate "field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please
give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Record Not found - Add New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.



.



.

Gerald:

Again, thanks! I made the suggested changes... now I can a msg box popping
up. It displays: "Type mismatch".

Any ideas what that means?

Tom



"Gerald Stanley" - wrote in message
...
Tom,

Try changing the DoCmd.OpenForm statement in the
AfterUpdate eventhandler to
DoCmd.OpenForm stDocName, , , stLinkCriteria, , Me![CboMoveTo]

In the Form frmNatoBodies, the Load eventhandler should
look like

Private Sub Form_Load()
[BodyName] = OpenArgs
End Sub

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gereald:

Again, thanks for helping me out on this.

Below the &&&&s are the 2 functions. In the
frmNatoBodies, I also added "=
OpenArgs" next to the event "On Load".

Again, nothing "different" really happens. If the
selected value from the
combo box does not find a matching record, I'm prompted
the click "Ok" for
"MsgBox "Not found: filtered?".

The selected value was NOT cached and automatically
transferred into the
field "BodyName". I understand that the code you
provided was not tested
(absolutely understandable w/o the application)... I
appreciate if you have
any other pointers as to how I could transfer the value
from the combo box
into the actual field if a matching record does not exist.

Thanks so much in advance,
Tom






&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo & """"

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If

On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

Private Sub CboMoveTo_NotInList(NewData As String,
Response As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes
Then DoCmd.OpenForm "frmNatoBodies", , , , , acDialog, NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

&&&&&&&&&

--
Thanks,
Tom


"Gerald Stanley" - wrote
in message
...
If you have coded only the NotInList and AfterUpdate
eventhandlers, then you should still be able to type into
the combo box's textbox and also see the dropdown list.
Could you confirm whether you are able to achieve either of
these actions and also post the code for the eventhandlers
back to this thread.

Gerald Stanley MCSD
-----Original Message-----
Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the combo
box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" - wrote
in message
...
You should handle the 'Not In List' scenarios by setting
the LimitToList property to Yes and coding the NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String,
Response
As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to
populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so
it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box
(CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it
will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event
function to
achieve the
following (when selecting a value for which NO record
exists).

1. If no record exists, cache the selected value
(from the
combo box) and
then automatically place it into the appropriate "field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please
give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Record Not found - Add New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.



.



.

Type mismatches occur when the data type of a variable
isn't the one that is expected e.g if you tried to populate
a long integer with a string.

It is probably because my DoCmd.OpenForm statement is
missing one comma. See if DoCmd.OpenForm stDocName, , ,
stLinkCriteria,,, Me![CboMoveTo] improves the situation.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald:

Again, thanks! I made the suggested changes... now I can
a msg box popping
up. It displays: "Type mismatch".

Any ideas what that means?

Tom



"Gerald Stanley" - wrote
in message
...
Tom,

Try changing the DoCmd.OpenForm statement in the
AfterUpdate eventhandler to
DoCmd.OpenForm stDocName, , , stLinkCriteria, ,
Me![CboMoveTo]

In the Form frmNatoBodies, the Load eventhandler should
look like

Private Sub Form_Load()
[BodyName] = OpenArgs
End Sub

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gereald:

Again, thanks for helping me out on this.

Below the &&&&s are the 2 functions. In the
frmNatoBodies, I also added "=
OpenArgs" next to the event "On Load".

Again, nothing "different" really happens. If the
selected value from the
combo box does not find a matching record, I'm prompted
the click "Ok" for
"MsgBox "Not found: filtered?".

The selected value was NOT cached and automatically
transferred into the
field "BodyName". I understand that the code you
provided was not tested
(absolutely understandable w/o the application)... I
appreciate if you have
any other pointers as to how I could transfer the value
from the combo box
into the actual field if a matching record does not exist.

Thanks so much in advance,
Tom






&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If

On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

Private Sub CboMoveTo_NotInList(NewData As String,
Response As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes
Then DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

&&&&&&&&&

--
Thanks,
Tom


"Gerald Stanley" - wrote
in message
...
If you have coded only the NotInList and AfterUpdate
eventhandlers, then you should still be able to type into
the combo box's textbox and also see the dropdown list.
Could you confirm whether you are able to achieve
either of
these actions and also post the code for the
eventhandlers
back to this thread.

Gerald Stanley MCSD
-----Original Message-----
Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the
combo
box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" -
wrote
in message
...
You should handle the 'Not In List' scenarios by
setting
the LimitToList property to Yes and coding the
NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String,
Response
As Integer)

If MsgBox("Do You Wish to continue with new
Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , ,
acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to
populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so
it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box
(CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up
another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it
will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event
function to
achieve the
following (when selecting a value for which NO record
exists).

1. If no record exists, cache the selected value
(from the
combo box) and
then automatically place it into the appropriate
"field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter
a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please
give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ &
Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Record Not found - Add New
Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.



.



.



.

Gerald:

Nothing has changed... when I a value for a non-existing record is selected,
the form pops up but the value is still not transferred from the combo box
into the actual data field (for storage).

I'm close to giving up on this. Thanks for you help anyhow.

--
Thanks,
Tom


"Gerald Stanley" - wrote in message
...
Type mismatches occur when the data type of a variable
isn't the one that is expected e.g if you tried to populate
a long integer with a string.

It is probably because my DoCmd.OpenForm statement is
missing one comma. See if DoCmd.OpenForm stDocName, , ,
stLinkCriteria,,, Me![CboMoveTo] improves the situation.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald:

Again, thanks! I made the suggested changes... now I can
a msg box popping
up. It displays: "Type mismatch".

Any ideas what that means?

Tom



"Gerald Stanley" - wrote
in message
...
Tom,

Try changing the DoCmd.OpenForm statement in the
AfterUpdate eventhandler to
DoCmd.OpenForm stDocName, , , stLinkCriteria, ,
Me![CboMoveTo]

In the Form frmNatoBodies, the Load eventhandler should
look like

Private Sub Form_Load()
[BodyName] = OpenArgs
End Sub

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gereald:

Again, thanks for helping me out on this.

Below the &&&&s are the 2 functions. In the
frmNatoBodies, I also added "=
OpenArgs" next to the event "On Load".

Again, nothing "different" really happens. If the
selected value from the
combo box does not find a matching record, I'm prompted
the click "Ok" for
"MsgBox "Not found: filtered?".

The selected value was NOT cached and automatically
transferred into the
field "BodyName". I understand that the code you
provided was not tested
(absolutely understandable w/o the application)... I
appreciate if you have
any other pointers as to how I could transfer the value
from the combo box
into the actual field if a matching record does not exist.

Thanks so much in advance,
Tom






&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If

On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

Private Sub CboMoveTo_NotInList(NewData As String,
Response As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes
Then DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

&&&&&&&&&

--
Thanks,
Tom


"Gerald Stanley" - wrote
in message
...
If you have coded only the NotInList and AfterUpdate
eventhandlers, then you should still be able to type into
the combo box's textbox and also see the dropdown list.
Could you confirm whether you are able to achieve
either of
these actions and also post the code for the
eventhandlers
back to this thread.

Gerald Stanley MCSD
-----Original Message-----
Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the
combo
box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" -
wrote
in message
...
You should handle the 'Not In List' scenarios by
setting
the LimitToList property to Yes and coding the
NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String,
Response
As Integer)

If MsgBox("Do You Wish to continue with new
Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , ,
acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to
populate the
control[BodyName]. You can do this in the form's Load
eventhandler along the lines of [BodyName] = OpenArgs.
Note that all the code above is untested aircode so
it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box
(CboMoveTo).

When selecting a value on the combo box from a source
table (which contains
let's say all "possible values"), it brings up
another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it
will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event
function to
achieve the
following (when selecting a value for which NO record
exists).

1. If no record exists, cache the selected value
(from the
combo box) and
then automatically place it into the appropriate
"field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter
a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please
give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ &
Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Record Not found - Add New
Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.



.



.



.

Something must have changed if you are now getting the form
to appear. Without seeing the app, it is hard to say what
the problem is. I would set a breakpoint on the
DoCmd.OpenForm statement and track the logic from there.
The key points would be to ensure that the combo box value
is appearing on the openArgs paramater then checking what
happens when the Load eventhandler fires on the called form.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald:

Nothing has changed... when I a value for a non-existing
record is selected,
the form pops up but the value is still not transferred
from the combo box
into the actual data field (for storage).

I'm close to giving up on this. Thanks for you help anyhow.

--
Thanks,
Tom


"Gerald Stanley" - wrote
in message
...
Type mismatches occur when the data type of a variable
isn't the one that is expected e.g if you tried to populate
a long integer with a string.

It is probably because my DoCmd.OpenForm statement is
missing one comma. See if DoCmd.OpenForm stDocName, , ,
stLinkCriteria,,, Me![CboMoveTo] improves the situation.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald:

Again, thanks! I made the suggested changes... now I can
a msg box popping
up. It displays: "Type mismatch".

Any ideas what that means?

Tom



"Gerald Stanley" - wrote
in message
...
Tom,

Try changing the DoCmd.OpenForm statement in the
AfterUpdate eventhandler to
DoCmd.OpenForm stDocName, , , stLinkCriteria, ,
Me![CboMoveTo]

In the Form frmNatoBodies, the Load eventhandler should
look like

Private Sub Form_Load()
[BodyName] = OpenArgs
End Sub

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gereald:

Again, thanks for helping me out on this.

Below the &&&&s are the 2 functions. In the
frmNatoBodies, I also added "=
OpenArgs" next to the event "On Load".

Again, nothing "different" really happens. If the
selected value from the
combo box does not find a matching record, I'm prompted
the click "Ok" for
"MsgBox "Not found: filtered?".

The selected value was NOT cached and automatically
transferred into the
field "BodyName". I understand that the code you
provided was not tested
(absolutely understandable w/o the application)... I
appreciate if you have
any other pointers as to how I could transfer the value
from the combo box
into the actual field if a matching record does not
exist.

Thanks so much in advance,
Tom






&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ & Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If

On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

Private Sub CboMoveTo_NotInList(NewData As String,
Response As Integer)

If MsgBox("Do You Wish to continue with new Value",
vbYesNo) = vbYes
Then DoCmd.OpenForm "frmNatoBodies", , , , , acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

&&&&&&&&&

--
Thanks,
Tom


"Gerald Stanley" -
wrote
in message
...
If you have coded only the NotInList and AfterUpdate
eventhandlers, then you should still be able to
type into
the combo box's textbox and also see the dropdown
list.
Could you confirm whether you are able to achieve
either of
these actions and also post the code for the
eventhandlers
back to this thread.

Gerald Stanley MCSD
-----Original Message-----
Gerald:

Thanks for the response...

I tried what you suggested, but when I click on the
combo
box now nothing
will happen.

Any ideas?

Tom


"Gerald Stanley" -
wrote
in message
...
You should handle the 'Not In List' scenarios by
setting
the LimitToList property to Yes and coding the
NotInList
eventhandler along the lines of

Private Sub CboMoveTo_NotInList(NewData As String,
Response
As Integer)

If MsgBox("Do You Wish to continue with new
Value",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNatoBodies", , , , ,
acDialog,
NewData
Response = acDataErrAdded
Else
CboMoveTo.Undo
Response = acDataErrContinue
End If
End Sub

You will also have to modify frmNatoBodies to
populate the
control[BodyName]. You can do this in the
form's Load
eventhandler along the lines of [BodyName] =
OpenArgs.
Note that all the code above is untested aircode so
it may
contain typos etc.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a form that contains only a combo box
(CboMoveTo).

When selecting a value on the combo box from a
source
table (which contains
let's say all "possible values"), it brings up
another
form with the
subordinate record data of the selected value.

If there is no record for the selected value, it
will then
prompt me w/ the
msg box "Record Not found - Add New Record?".


I need help w/ modifying the AfterUpdate event
function to
achieve the
following (when selecting a value for which NO
record
exists).

1. If no record exists, cache the selected value
(from the
combo box) and
then automatically place it into the appropriate
"field"
[BodyName] on the
subform that contains the record data.

2. If no record exist and I do NOT want to enter
a new
record, have the
option to cancel out of this operation.


Is that possible? If so, does anyone could please
give me
some pointers as
to how I need to modify the function below?


Thanks in advance,
Tom



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub CboMoveTo_AfterUpdate()

' Record Selection
If Not IsNull(Me.CboMoveTo) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[BodyName] = """ &
Me.CboMoveTo &
""""

If rs.NoMatch Then
MsgBox "Record Not found - Add New
Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNatoBodies"

stLinkCriteria = "[BodyName]=" & "'" &
Me![CboMoveTo] & "'"
DoCmd.OpenForm stDocName, , ,
stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


.



.



.



.



.