NotInList Problem

Wayne, I finally got it to work. I know it probably wouldn't be suggested,
but I think it will work for the users. I used the dialog box as
recommended and used a list box on the form so that it opens based on the
"Type". The user can then see the complete listing of type chosen and then
add the next available number to the list. You stated previously "You can
auto fill two of the textboxes
with the value you typed into the combo box on the main form and the value
of Forms![f*Manufacturing]!ComboType. This will leave the third textbox to
fill in." Based on code used below, will you please tell me how to do this?


Private Sub ComboAssyDescription_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ComboAssyDescription_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fAssemblyDescriptions"
stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] & "'"


DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd, acDialog

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub


Thanks, Pam








"Wayne Morgan" wrote in message
...
You should be able to calculate the number. Break the string apart to get
just the number, add one, then concatenate it back together. IF the number
is always 2 digits, it's easy, just take the Right(string, 2) to get the
last 2 characters of the string, add one, then format it as 2 digits when
you concatenate back.

Since you're using 2 digits (i.e. leading zeros), getting the DMax() of
the field should give you the last entry.

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,
Again, thanks for your time and help with this. I have other forms set
up this way. The only problem with this one is that I need for users to
see the last entry in the Assy Sub field so that they would know what the
next number is. This is not the autonumber field for record numbers.
Please see msg with example text for fields. Whether to enter Stator 04
if Stator 03 was the last entry. I'm not sure how to resolve or work
around this scenario.

In the Load event of fAssemblyDescriptions, you would assign the values to
the textboxes.

Example:
Me.txtType = [Forms]![fManufacturing]![ComboType]

You can also pass the values in the OpenArgs argument of the DoCmd.OpenForm
call and break it apart again in the pop-up's Load event.

Example:
strOpenArgs = [Forms]![fManufacturing]![ComboType] & "," & NewData
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd, acDialog,
strOpenArgs

Then in the Load event:
Dim strSplit() As String
strSplit = Split(OpenArgs, ",")
Me.txtType = strSplit(0)
Me.txtNewData = strSplit(1)

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne, I finally got it to work. I know it probably wouldn't be
suggested, but I think it will work for the users. I used the dialog box
as recommended and used a list box on the form so that it opens based on
the "Type". The user can then see the complete listing of type chosen
and then add the next available number to the list. You stated previously
"You can auto fill two of the textboxes
with the value you typed into the combo box on the main form and the value
of Forms![f*Manufacturing]!ComboType. This will leave the third textbox to
fill in." Based on code used below, will you please tell me how to do
this?


Private Sub ComboAssyDescription_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ComboAssyDescription_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fAssemblyDescriptions"
stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] & "'"


DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd, acDialog

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub


Thanks, Pam








"Wayne Morgan" wrote in
message ...
You should be able to calculate the number. Break the string apart to get
just the number, add one, then concatenate it back together. IF the
number is always 2 digits, it's easy, just take the Right(string, 2) to
get the last 2 characters of the string, add one, then format it as 2
digits when you concatenate back.

Since you're using 2 digits (i.e. leading zeros), getting the DMax() of
the field should give you the last entry.

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,
Again, thanks for your time and help with this. I have other forms set
up this way. The only problem with this one is that I need for users to
see the last entry in the Assy Sub field so that they would know what
the next number is. This is not the autonumber field for record
numbers. Please see msg with example text for fields. Whether to enter
Stator 04 if Stator 03 was the last entry. I'm not sure how to resolve
or work around this scenario.

Wayne,

This is what I typed in:

Private Sub Form_Load()
Me.AssyType = [Forms]![fManufacturing]![ComboType]
End Sub

and I get message "can't find form fManufacturing.
Thanks, Pam


"Wayne Morgan" wrote in message
...
In the Load event of fAssemblyDescriptions, you would assign the values to
the textboxes.

Example:
Me.txtType = [Forms]![fManufacturing]![ComboType]

You can also pass the values in the OpenArgs argument of the
DoCmd.OpenForm call and break it apart again in the pop-up's Load event.

Example:
strOpenArgs = [Forms]![fManufacturing]![ComboType] & "," & NewData
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd, acDialog,
strOpenArgs

Then in the Load event:
Dim strSplit() As String
strSplit = Split(OpenArgs, ",")
Me.txtType = strSplit(0)
Me.txtNewData = strSplit(1)

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne, I finally got it to work. I know it probably wouldn't be
suggested, but I think it will work for the users. I used the dialog box
as recommended and used a list box on the form so that it opens based on
the "Type". The user can then see the complete listing of type chosen
and then add the next available number to the list. You stated
previously "You can auto fill two of the textboxes
with the value you typed into the combo box on the main form and the
value
of Forms![f*Manufacturing]!ComboType. This will leave the third textbox
to
fill in." Based on code used below, will you please tell me how to do
this?


Private Sub ComboAssyDescription_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_ComboAssyDescription_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fAssemblyDescriptions"
stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] &
"'"


DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd, acDialog

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub


Thanks, Pam








"Wayne Morgan" wrote in
message ...
You should be able to calculate the number. Break the string apart to
get just the number, add one, then concatenate it back together. IF the
number is always 2 digits, it's easy, just take the Right(string, 2) to
get the last 2 characters of the string, add one, then format it as 2
digits when you concatenate back.

Since you're using 2 digits (i.e. leading zeros), getting the DMax() of
the field should give you the last entry.

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,
Again, thanks for your time and help with this. I have other forms set
up this way. The only problem with this one is that I need for users
to see the last entry in the Assy Sub field so that they would know
what the next number is. This is not the autonumber field for record
numbers. Please see msg with example text for fields. Whether to enter
Stator 04 if Stator 03 was the last entry. I'm not sure how to resolve
or work around this scenario.

The form has to be open to get the value from it. It should be open since it
is the same form you're using in the line

stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] & "'"

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,

This is what I typed in:

Private Sub Form_Load()
Me.AssyType = [Forms]![fManufacturing]![ComboType]
End Sub

and I get message "can't find form fManufacturing.
Thanks, Pam


"Wayne Morgan" wrote in
message ...
In the Load event of fAssemblyDescriptions, you would assign the values
to the textboxes.

Example:
Me.txtType = [Forms]![fManufacturing]![ComboType]

You can also pass the values in the OpenArgs argument of the
DoCmd.OpenForm call and break it apart again in the pop-up's Load event.

Example:
strOpenArgs = [Forms]![fManufacturing]![ComboType] & "," & NewData
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd,
acDialog, strOpenArgs

Then in the Load event:
Dim strSplit() As String
strSplit = Split(OpenArgs, ",")
Me.txtType = strSplit(0)
Me.txtNewData = strSplit(1)

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne, I finally got it to work. I know it probably wouldn't be
suggested, but I think it will work for the users. I used the dialog
box as recommended and used a list box on the form so that it opens
based on the "Type". The user can then see the complete listing of
type chosen and then add the next available number to the list. You
stated previously "You can auto fill two of the textboxes
with the value you typed into the combo box on the main form and the
value
of Forms![f*Manufacturing]!ComboType. This will leave the third textbox
to
fill in." Based on code used below, will you please tell me how to do
this?


Private Sub ComboAssyDescription_NotInList(NewData As String, Response
As Integer)
On Error GoTo Err_ComboAssyDescription_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fAssemblyDescriptions"
stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] &
"'"


DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd,
acDialog

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub


Thanks, Pam








"Wayne Morgan" wrote in
message ...
You should be able to calculate the number. Break the string apart to
get just the number, add one, then concatenate it back together. IF the
number is always 2 digits, it's easy, just take the Right(string, 2) to
get the last 2 characters of the string, add one, then format it as 2
digits when you concatenate back.

Since you're using 2 digits (i.e. leading zeros), getting the DMax() of
the field should give you the last entry.

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,
Again, thanks for your time and help with this. I have other forms
set up this way. The only problem with this one is that I need for
users to see the last entry in the Assy Sub field so that they would
know what the next number is. This is not the autonumber field for
record numbers. Please see msg with example text for fields. Whether
to enter Stator 04 if Stator 03 was the last entry. I'm not sure how
to resolve or work around this scenario.

Wayne,
Thank you soooo much! I don't know what I did, but I entered again and it
worked. I've been trying for quite some time to get this worked out. I
really appreciate your time and effort!!! I almost hate to ask, but would
you mind taking a look at another post I entered on March 23 "On Click Code
Problem". Someone responded and the solution given did not work and I
haven't received a reply back. Your explanations are so easy to understand
I'm hoping you won't mind helping with this as well. Thanks again, Pam.

"Wayne Morgan" wrote in message
...
The form has to be open to get the value from it. It should be open since
it is the same form you're using in the line

stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] & "'"

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,

This is what I typed in:

Private Sub Form_Load()
Me.AssyType = [Forms]![fManufacturing]![ComboType]
End Sub

and I get message "can't find form fManufacturing.
Thanks, Pam


"Wayne Morgan" wrote in
message ...
In the Load event of fAssemblyDescriptions, you would assign the values
to the textboxes.

Example:
Me.txtType = [Forms]![fManufacturing]![ComboType]

You can also pass the values in the OpenArgs argument of the
DoCmd.OpenForm call and break it apart again in the pop-up's Load event.

Example:
strOpenArgs = [Forms]![fManufacturing]![ComboType] & "," & NewData
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd,
acDialog, strOpenArgs

Then in the Load event:
Dim strSplit() As String
strSplit = Split(OpenArgs, ",")
Me.txtType = strSplit(0)
Me.txtNewData = strSplit(1)

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne, I finally got it to work. I know it probably wouldn't be
suggested, but I think it will work for the users. I used the dialog
box as recommended and used a list box on the form so that it opens
based on the "Type". The user can then see the complete listing of
type chosen and then add the next available number to the list. You
stated previously "You can auto fill two of the textboxes
with the value you typed into the combo box on the main form and the
value
of Forms![f*Manufacturing]!ComboType. This will leave the third textbox
to
fill in." Based on code used below, will you please tell me how to do
this?


Private Sub ComboAssyDescription_NotInList(NewData As String, Response
As Integer)
On Error GoTo Err_ComboAssyDescription_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fAssemblyDescriptions"
stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] &
"'"


DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd,
acDialog

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub


Thanks, Pam








"Wayne Morgan" wrote in
message ...
You should be able to calculate the number. Break the string apart to
get just the number, add one, then concatenate it back together. IF
the number is always 2 digits, it's easy, just take the Right(string,
2) to get the last 2 characters of the string, add one, then format it
as 2 digits when you concatenate back.

Since you're using 2 digits (i.e. leading zeros), getting the DMax()
of the field should give you the last entry.

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,
Again, thanks for your time and help with this. I have other forms
set up this way. The only problem with this one is that I need for
users to see the last entry in the Assy Sub field so that they would
know what the next number is. This is not the autonumber field for
record numbers. Please see msg with example text for fields. Whether
to enter Stator 04 if Stator 03 was the last entry. I'm not sure how
to resolve or work around this scenario.

merhaba


"Pam" , haber iletisinde þunlarý
...
Wayne,
Thank you soooo much! I don't know what I did, but I entered again and it
worked. I've been trying for quite some time to get this worked out. I
really appreciate your time and effort!!! I almost hate to ask, but would
you mind taking a look at another post I entered on March 23 "On Click
Code Problem". Someone responded and the solution given did not work and
I haven't received a reply back. Your explanations are so easy to
understand I'm hoping you won't mind helping with this as well. Thanks
again, Pam.

"Wayne Morgan" wrote in
message ...
The form has to be open to get the value from it. It should be open since
it is the same form you're using in the line

stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] &
"'"

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,

This is what I typed in:

Private Sub Form_Load()
Me.AssyType = [Forms]![fManufacturing]![ComboType]
End Sub

and I get message "can't find form fManufacturing.
Thanks, Pam


"Wayne Morgan" wrote in
message ...
In the Load event of fAssemblyDescriptions, you would assign the values
to the textboxes.

Example:
Me.txtType = [Forms]![fManufacturing]![ComboType]

You can also pass the values in the OpenArgs argument of the
DoCmd.OpenForm call and break it apart again in the pop-up's Load
event.

Example:
strOpenArgs = [Forms]![fManufacturing]![ComboType] & "," & NewData
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd,
acDialog, strOpenArgs

Then in the Load event:
Dim strSplit() As String
strSplit = Split(OpenArgs, ",")
Me.txtType = strSplit(0)
Me.txtNewData = strSplit(1)

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne, I finally got it to work. I know it probably wouldn't be
suggested, but I think it will work for the users. I used the dialog
box as recommended and used a list box on the form so that it opens
based on the "Type". The user can then see the complete listing of
type chosen and then add the next available number to the list. You
stated previously "You can auto fill two of the textboxes
with the value you typed into the combo box on the main form and the
value
of Forms![f*Manufacturing]!ComboType. This will leave the third
textbox to
fill in." Based on code used below, will you please tell me how to do
this?


Private Sub ComboAssyDescription_NotInList(NewData As String, Response
As Integer)
On Error GoTo Err_ComboAssyDescription_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fAssemblyDescriptions"
stLinkCriteria = "AssyType='" & [Forms]![fManufacturing]![ComboType] &
"'"


DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd,
acDialog

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub


Thanks, Pam








"Wayne Morgan" wrote in
message ...
You should be able to calculate the number. Break the string apart to
get just the number, add one, then concatenate it back together. IF
the number is always 2 digits, it's easy, just take the Right(string,
2) to get the last 2 characters of the string, add one, then format
it as 2 digits when you concatenate back.

Since you're using 2 digits (i.e. leading zeros), getting the DMax()
of the field should give you the last entry.

--
Wayne Morgan
MS Access MVP


"Pam" wrote in message
...
Wayne,
Again, thanks for your time and help with this. I have other forms
set up this way. The only problem with this one is that I need for
users to see the last entry in the Assy Sub field so that they would
know what the next number is. This is not the autonumber field for
record numbers. Please see msg with example text for fields.
Whether to enter Stator 04 if Stator 03 was the last entry. I'm not
sure how to resolve or work around this scenario.