NotInList Problem

[Pam]

Using the following code, will someone please let me know why I'm still
getting an error msg "Text you entered isn't an item in list"

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

Dim db As DAO.Database
Dim rst As DAO.Recordset

'Prompt user to verify they wish to add new value.
If MsgBox("Do you want to add '" & NewData & "' as a new title?", vbYesNo,
"Add New Item?") = vbYes Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tAssemblyDescriptions")
'this adds the NewData to the table
With rst
.AddNew ' new record
!AssyDescription = NewData
.Update ' save it
End With
' close the recordset
rst.Close
' cleanup
Set rst = Nothing
Set db = Nothing

' Continue without displaying default error message.
' This also does an automatic requery
Response = acDataErrAdded
Else
' don't add & continue
Response = adDataErrContinue
' clear the combo box
Me.ComboDescription.Undo
End If
End Sub

I use this same code in several other places in the db and it works well.
All I did was copy and made changes to reflect names as needed.

Any help is greatly appreciated!
Thanks in advance,
Pam

 

[Wayne Morgan]

Is there more than one field in the table? If so, how many of them are
returned by the Row Source's query? Are there any filters in the query that
would prevent the new value from being returned? Your code is only adding
data to one field. If there are 2 fields and the other is an autonumber, it
will fill itself in, so this isn't a problem. However, if there are other
fields that are being left Null when you create the new record and you're
not returning Null values in the query, you may be excluding the value you
just entered.

 

[Pam]

Wayne,
Thank you for your reply. Below is the Row Source for the combo box. I did
have two select fields. I didn't think about that. I took out the AssySub
field from the Select portion and removed the complete Order By clause and I
still received the same error msg - item not in list. Thanks again for your
help! Pam

SELECT tAssemblyDescriptions.AssyDescription, tAssemblyDescriptions.AssySub
FROM tAssemblyDescriptions WHERE
(((tAssemblyDescriptions.AssyType)=Forms![f*Manufacturing]!ComboType)) ORDER
By tAssyDescriptions,tAssemblyDescriptions.AssySub;

 

[Wayne Morgan]

While you're returning 2 columns, you're actually using 3 fields from the
source table, AssyDescription, AssySub, and AssyType. The only one you're
supplying a value for when you add the new item is AssyDescription
(!AssyDescription = NewData). This new record you have will not have a value
for AssyType. The query you're using is limiting the records returned to
those whose AssyType matches Forms![f*Manufacturing]!ComboType (the WHERE
clause of the query). Since you haven't supplied a value for this, I suspect
it doesn't match and is therefore not returned.

Amended NotInList code:
With rst
.AddNew ' new record
!AssyDescription = NewData
!AssyType = Forms![f*Manufacturing]!ComboType
!AssySub = 'fill in the appropriate value here
.Update ' save it
End With

Since you're trying to add it with the ComboType selected, I assume what
you're adding should be the same type. If not, it still won't be in the list
after you add it.

--
Wayne Morgan
MS Access MVP

Wayne,
Thank you for your reply. Below is the Row Source for the combo box. I
did have two select fields. I didn't think about that. I took out the
AssySub field from the Select portion and removed the complete Order By
clause and I still received the same error msg - item not in list. Thanks
again for your help! Pam

SELECT tAssemblyDescriptions.AssyDescription,
tAssemblyDescriptions.AssySub FROM tAssemblyDescriptions WHERE
(((tAssemblyDescriptions.AssyType)=Forms![f*Manufacturing]!ComboType))
ORDER By tAssyDescriptions,tAssemblyDescriptions.AssySub;

 

[Pam]

Wayne,
Thank you so much for your easy to understand explanation. I see what you
mean about the other fields not being filled in, but I find I will need to
have those filled in as well. !AssySub = 'fill in appropriate value here
may not work in this case. I'm not sure how, or if, the next text and
number would be entered (Stator 03). Please see my example below:

Assy Type AssyTypeSub Assy Description
Stator Assy Stator 01 Grinding
Stator Assy Stator 02 Welding

I thought I could open the form in datasheet view with AssyType = to
ComboType on main form, so that user would be able to enter new item to list
as well as see the next number in sequence.
I tried the code below, but received message "Syntax error (missing
operator) in query expression 'AssyType=Stator Assy'. and the complete DoCmd
line in code is highlighted. I'm sure it's something quite simple, but I
find aspects of code to be very confusing. If you wouldn't mind taking a
look at the code below to see what the problem could be, I would certainly
be grateful. Thanks again for your help! Pam

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 = "fNikkisoAssemblyDescriptions"
stLinkCriteria = "AssyType=" &
[Forms]![f*NikkisoManufacturing]![ComboNikkisoType]

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormEdit,
acWindowNormal

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub

While you're returning 2 columns, you're actually using 3 fields from the
source table, AssyDescription, AssySub, and AssyType. The only one you're
supplying a value for when you add the new item is AssyDescription
(!AssyDescription = NewData). This new record you have will not have a
value for AssyType. The query you're using is limiting the records
returned to those whose AssyType matches Forms![f*Manufacturing]!ComboType
(the WHERE clause of the query). Since you haven't supplied a value for
this, I suspect it doesn't match and is therefore not returned.

Amended NotInList code:
With rst
.AddNew ' new record
!AssyDescription = NewData
!AssyType = Forms![f*Manufacturing]!ComboType
!AssySub = 'fill in the appropriate value here
.Update ' save it
End With

Since you're trying to add it with the ComboType selected, I assume what
you're adding should be the same type. If not, it still won't be in the
list after you add it.

--
Wayne Morgan
MS Access MVP

Wayne,
Thank you for your reply. Below is the Row Source for the combo box. I
did have two select fields. I didn't think about that. I took out the
AssySub field from the Select portion and removed the complete Order By
clause and I still received the same error msg - item not in list.
Thanks again for your help! Pam

SELECT tAssemblyDescriptions.AssyDescription,
tAssemblyDescriptions.AssySub FROM tAssemblyDescriptions WHERE
(((tAssemblyDescriptions.AssyType)=Forms![f*Manufacturing]!ComboType))
ORDER By tAssyDescriptions,tAssemblyDescriptions.AssySub;

Is there more than one field in the table? If so, how many of them are
returned by the Row Source's query? Are there any filters in the query
that would prevent the new value from being returned? Your code is only
adding data to one field. If there are 2 fields and the other is an
autonumber, it will fill itself in, so this isn't a problem. However, if
there are other fields that are being left Null when you create the new
record and you're not returning Null values in the query, you may be
excluding the value you just entered.

--
Wayne Morgan
MS Access MVP


Using the following code, will someone please let me know why I'm still
getting an error msg "Text you entered isn't an item in list"

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

Dim db As DAO.Database
Dim rst As DAO.Recordset

'Prompt user to verify they wish to add new value.
If MsgBox("Do you want to add '" & NewData & "' as a new title?",
vbYesNo, "Add New Item?") = vbYes Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tAssemblyDescriptions")
'this adds the NewData to the table
With rst
.AddNew ' new record
!AssyDescription = NewData
.Update ' save it
End With
' close the recordset
rst.Close
' cleanup
Set rst = Nothing
Set db = Nothing

' Continue without displaying default error message.
' This also does an automatic requery
Response = acDataErrAdded
Else
' don't add & continue
Response = adDataErrContinue
' clear the combo box
Me.ComboDescription.Undo
End If
End Sub

I use this same code in several other places in the db and it works
well. All I did was copy and made changes to reflect names as needed.

Any help is greatly appreciated!
Thanks in advance,
Pam

 

[Wayne Morgan]

AssyType is text, not a number. Try this syntax:

stLinkCriteria = "AssyType='" &
[Forms]![f*NikkisoManufacturing]![ComboNikkisoType] & "'"

--
Wayne Morgan
MS Access MVP

Wayne,
Thank you so much for your easy to understand explanation. I see what you
mean about the other fields not being filled in, but I find I will need to
have those filled in as well. !AssySub = 'fill in appropriate value here
may not work in this case. I'm not sure how, or if, the next text and
number would be entered (Stator 03). Please see my example below:

Assy Type AssyTypeSub Assy Description
Stator Assy Stator 01 Grinding
Stator Assy Stator 02 Welding

I thought I could open the form in datasheet view with AssyType = to
ComboType on main form, so that user would be able to enter new item to
list as well as see the next number in sequence.
I tried the code below, but received message "Syntax error (missing
operator) in query expression 'AssyType=Stator Assy'. and the complete
DoCmd line in code is highlighted. I'm sure it's something quite simple,
but I find aspects of code to be very confusing. If you wouldn't mind
taking a look at the code below to see what the problem could be, I would
certainly be grateful. Thanks again for your help! Pam

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 = "fNikkisoAssemblyDescriptions"
stLinkCriteria = "AssyType=" &
[Forms]![f*NikkisoManufacturing]![ComboNikkisoType]

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormEdit,
acWindowNormal

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub

While you're returning 2 columns, you're actually using 3 fields from the
source table, AssyDescription, AssySub, and AssyType. The only one you're
supplying a value for when you add the new item is AssyDescription
(!AssyDescription = NewData). This new record you have will not have a
value for AssyType. The query you're using is limiting the records
returned to those whose AssyType matches
Forms![f*Manufacturing]!ComboType (the WHERE clause of the query). Since
you haven't supplied a value for this, I suspect it doesn't match and is
therefore not returned.

Amended NotInList code:
With rst
.AddNew ' new record
!AssyDescription = NewData
!AssyType = Forms![f*Manufacturing]!ComboType
!AssySub = 'fill in the appropriate value here
.Update ' save it
End With

Since you're trying to add it with the ComboType selected, I assume what
you're adding should be the same type. If not, it still won't be in the
list after you add it.

--
Wayne Morgan
MS Access MVP

Wayne,
Thank you for your reply. Below is the Row Source for the combo box. I
did have two select fields. I didn't think about that. I took out the
AssySub field from the Select portion and removed the complete Order By
clause and I still received the same error msg - item not in list.
Thanks again for your help! Pam

SELECT tAssemblyDescriptions.AssyDescription,
tAssemblyDescriptions.AssySub FROM tAssemblyDescriptions WHERE
(((tAssemblyDescriptions.AssyType)=Forms![f*Manufacturing]!ComboType))
ORDER By tAssyDescriptions,tAssemblyDescriptions.AssySub;






message Is there more than one field in the table? If so, how many of them are
returned by the Row Source's query? Are there any filters in the query
that would prevent the new value from being returned? Your code is only
adding data to one field. If there are 2 fields and the other is an
autonumber, it will fill itself in, so this isn't a problem. However,
if there are other fields that are being left Null when you create the
new record and you're not returning Null values in the query, you may
be excluding the value you just entered.

--
Wayne Morgan
MS Access MVP


Using the following code, will someone please let me know why I'm
still getting an error msg "Text you entered isn't an item in list"

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

Dim db As DAO.Database
Dim rst As DAO.Recordset

'Prompt user to verify they wish to add new value.
If MsgBox("Do you want to add '" & NewData & "' as a new title?",
vbYesNo, "Add New Item?") = vbYes Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tAssemblyDescriptions")
'this adds the NewData to the table
With rst
.AddNew ' new record
!AssyDescription = NewData
.Update ' save it
End With
' close the recordset
rst.Close
' cleanup
Set rst = Nothing
Set db = Nothing

' Continue without displaying default error message.
' This also does an automatic requery
Response = acDataErrAdded
Else
' don't add & continue
Response = adDataErrContinue
' clear the combo box
Me.ComboDescription.Undo
End If
End Sub

I use this same code in several other places in the db and it works
well. All I did was copy and made changes to reflect names as needed.

Any help is greatly appreciated!
Thanks in advance,
Pam

 

[Pam]

Thanks Wayne. I can now get to the form in datasheet view, but it opens
with the message "Text isn't an item in list" and I must "OK" out of that
first. Then, I enter the data, close and go back to the main form, the
cursor is still on the entry and whether I tab or return, it goes back to
the datasheet form. Any ideas? Thanks, Pam

AssyType is text, not a number. Try this syntax:

stLinkCriteria = "AssyType='" &
[Forms]![f*NikkisoManufacturing]![ComboNikkisoType] & "'"

--
Wayne Morgan
MS Access MVP

Wayne,
Thank you so much for your easy to understand explanation. I see what
you mean about the other fields not being filled in, but I find I will
need to have those filled in as well. !AssySub = 'fill in appropriate
value here may not work in this case. I'm not sure how, or if, the next
text and number would be entered (Stator 03). Please see my example
below:

Assy Type AssyTypeSub Assy Description
Stator Assy Stator 01 Grinding
Stator Assy Stator 02 Welding

I thought I could open the form in datasheet view with AssyType = to
ComboType on main form, so that user would be able to enter new item to
list as well as see the next number in sequence.
I tried the code below, but received message "Syntax error (missing
operator) in query expression 'AssyType=Stator Assy'. and the complete
DoCmd line in code is highlighted. I'm sure it's something quite simple,
but I find aspects of code to be very confusing. If you wouldn't mind
taking a look at the code below to see what the problem could be, I would
certainly be grateful. Thanks again for your help! Pam

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 = "fNikkisoAssemblyDescriptions"
stLinkCriteria = "AssyType=" &
[Forms]![f*NikkisoManufacturing]![ComboNikkisoType]

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormEdit,
acWindowNormal

Response = acDataErrAdded

Exit_ComboAssyDescription_NotInList:
Exit Sub

Err_ComboAssyDescription_NotInList:
MsgBox Err.Description
Resume Exit_ComboAssyDescription_NotInList
End Sub

While you're returning 2 columns, you're actually using 3 fields from
the source table, AssyDescription, AssySub, and AssyType. The only one
you're supplying a value for when you add the new item is
AssyDescription (!AssyDescription = NewData). This new record you have
will not have a value for AssyType. The query you're using is limiting
the records returned to those whose AssyType matches
Forms![f*Manufacturing]!ComboType (the WHERE clause of the query). Since
you haven't supplied a value for this, I suspect it doesn't match and is
therefore not returned.

Amended NotInList code:
With rst
.AddNew ' new record
!AssyDescription = NewData
!AssyType = Forms![f*Manufacturing]!ComboType
!AssySub = 'fill in the appropriate value here
.Update ' save it
End With

Since you're trying to add it with the ComboType selected, I assume what
you're adding should be the same type. If not, it still won't be in the
list after you add it.

--
Wayne Morgan
MS Access MVP


Wayne,
Thank you for your reply. Below is the Row Source for the combo box. I
did have two select fields. I didn't think about that. I took out the
AssySub field from the Select portion and removed the complete Order By
clause and I still received the same error msg - item not in list.
Thanks again for your help! Pam

SELECT tAssemblyDescriptions.AssyDescription,
tAssemblyDescriptions.AssySub FROM tAssemblyDescriptions WHERE
(((tAssemblyDescriptions.AssyType)=Forms![f*Manufacturing]!ComboType))
ORDER By tAssyDescriptions,tAssemblyDescriptions.AssySub;






message Is there more than one field in the table? If so, how many of them are
returned by the Row Source's query? Are there any filters in the query
that would prevent the new value from being returned? Your code is
only adding data to one field. If there are 2 fields and the other is
an autonumber, it will fill itself in, so this isn't a problem.
However, if there are other fields that are being left Null when you
create the new record and you're not returning Null values in the
query, you may be excluding the value you just entered.

--
Wayne Morgan
MS Access MVP


Using the following code, will someone please let me know why I'm
still getting an error msg "Text you entered isn't an item in list"

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

Dim db As DAO.Database
Dim rst As DAO.Recordset

'Prompt user to verify they wish to add new value.
If MsgBox("Do you want to add '" & NewData & "' as a new title?",
vbYesNo, "Add New Item?") = vbYes Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tAssemblyDescriptions")
'this adds the NewData to the table
With rst
.AddNew ' new record
!AssyDescription = NewData
.Update ' save it
End With
' close the recordset
rst.Close
' cleanup
Set rst = Nothing
Set db = Nothing

' Continue without displaying default error message.
' This also does an automatic requery
Response = acDataErrAdded
Else
' don't add & continue
Response = adDataErrContinue
' clear the combo box
Me.ComboDescription.Undo
End If
End Sub

I use this same code in several other places in the db and it works
well. All I did was copy and made changes to reflect names as needed.

Any help is greatly appreciated!
Thanks in advance,
Pam

 

[Wayne Morgan]

The form you're popping up to enter the data should be opened in Dialog mode
with 3 textboxes to add the new item. 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. Opening the form in dialog mode will cause the NotInList event on
the main form to pause until you close the popup form. You would then use
the Response=acDataErrAdded, just as you are now.

To open the popup form in dialog mode, set the window mode argument of the
DoCmd.OpenForm call to acDialog.

 

[Pam]

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. Thanks, Pam

The form you're popping up to enter the data should be opened in Dialog
mode with 3 textboxes to add the new item. 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. Opening the form in dialog mode will cause the
NotInList event on the main form to pause until you close the popup form.
You would then use the Response=acDataErrAdded, just as you are now.

To open the popup form in dialog mode, set the window mode argument of the
DoCmd.OpenForm call to acDialog.

--
Wayne Morgan
MS Access MVP

Thanks Wayne. I can now get to the form in datasheet view, but it opens
with the message "Text isn't an item in list" and I must "OK" out of that
first. Then, I enter the data, close and go back to the main form, the
cursor is still on the entry and whether I tab or return, it goes back to
the datasheet form. Any ideas?

 

[Wayne Morgan]

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.

 

[Pam]

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]

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

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

 

[Pam]

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

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

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

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


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 Morgan]

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

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

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

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








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


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.

 

[Pam]

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.

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

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

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


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








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


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.

 

[Onur GÖKGÖZ]

merhaba

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.

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

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


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


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








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


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.