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
Wayne Morgan said:
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