using multiple fields from a table

  • Thread starter Thread starter shymousee
  • Start date Start date
S

shymousee

I am trying to use one table with multiple fields in a notinlist pop up. The
name of the table is equipment. It has 2 fields in it, equipment and toners.
No matter what I try, I get an error message saying the item is not on the
list. If I change the statement
strSQL = "INSERT INTO equipment([equipment]) " & _
"VALUES ('" & NewData & "');"
to

strSQL = "INSERT INTO equipment([toners]) " & _
"VALUES ('" & NewData & "');"

I get the message, The text you entered isn't an item in the list. Select
an item from the list, or enter text that matches one of the listed items.
This is after I get a message saying the item was added.

I am trying to combine some tables instead of having a bunch of tables for
each time I want to use the notinlist option.

Private Sub toner_NotInList(NewData As String, Response As Integer)
On Error GoTo equipment_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The equipment " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "property")
If intAnswer = vbYes Then
strSQL = "INSERT INTO equipment([equipment]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new equipment has been added to the list." _
, vbInformation, "property"
Response = acDataErrAdded
Else
MsgBox "Please choose a equipment from the list." _
, vbInformation, "property"
Response = acDataErrContinue
End If
equipment_NotInList_Exit:
Exit Sub
equipment_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume equipment_NotInList_Exit
End Sub
 
How depends on what ... and I don't have a very clear picture of what data
you are working with underneath the form you seem to be trying to do this
in...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
I am trying to keep track of the toner that we purchase. Since I already had
a table that contained the equipment that we purchased, I thought I would add
another field to the equipment table and this would eliminate a table.

The form I am working with contains the name of the toner, what printer the
toner is for,the cost of the toner, shipping, total of the order, the amount
of toner ordered and how many have been used. I have a formula that
calculates the amount of the order.

I would like to be able to use the second field in the equipment table to
add the toner information.

I hope this helps explain things more.

Jeff Boyce said:
How depends on what ... and I don't have a very clear picture of what data
you are working with underneath the form you seem to be trying to do this
in...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

shymousee said:
I am trying to use one table with multiple fields in a notinlist pop up.
The
name of the table is equipment. It has 2 fields in it, equipment and
toners.
No matter what I try, I get an error message saying the item is not on the
list. If I change the statement
strSQL = "INSERT INTO equipment([equipment]) " & _
"VALUES ('" & NewData & "');"
to

strSQL = "INSERT INTO equipment([toners]) " & _
"VALUES ('" & NewData & "');"

I get the message, The text you entered isn't an item in the list. Select
an item from the list, or enter text that matches one of the listed items.
This is after I get a message saying the item was added.

I am trying to combine some tables instead of having a bunch of tables for
each time I want to use the notinlist option.

Private Sub toner_NotInList(NewData As String, Response As Integer)
On Error GoTo equipment_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The equipment " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "property")
If intAnswer = vbYes Then
strSQL = "INSERT INTO equipment([equipment]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new equipment has been added to the list." _
, vbInformation, "property"
Response = acDataErrAdded
Else
MsgBox "Please choose a equipment from the list." _
, vbInformation, "property"
Response = acDataErrContinue
End If
equipment_NotInList_Exit:
Exit Sub
equipment_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume equipment_NotInList_Exit
End Sub
Click to expand...


.
Click to expand...
 
I am trying to keep track of the toner that we purchase. Since I already had
a table that contained the equipment that we purchased, I thought I would add
another field to the equipment table and this would eliminate a table.
Click to expand...

Is the toner in fact an attribute of the equipment?

Each Table should contain information about a particular type of Entity (a
real-life person, thing or event). Each record should represent one instance
of that Entity, and its fields would be Attributes of that entity; you
wouldn't - usually - have an (Equipment or Toner) entity, since in my mind
they're very different kinds of things. You may be using false economy here to
save a table.

What are the fields in your current table?
 
Back
Top