Opposite Not In List

[Guest]

Hi

I have a data base where tools can be used to more than one job.
Data are organized in two tables:

tblTools with fields ToolID (auto), ToolName, Supplier, Shared (Y,N) and
tblJobs with JobID (auto), fkToolID (related), JobInfo, StartDate, EndDate
All fields property “Required†is set to “Yesâ€

The way I want to have the data entry is by using a single form, in
continuous view, (not two forms or Jobs subform of tools) and enter data for
Tools and Jobs on the fields:

ToolName, Supplier, Shared, JobInfo, StartDate, EndDate

I want to be able to type a ToolName in a textbox or combobox and then:

I) If that tool is found in the Tools table a msgbox asks if I want to use
it again or not.
-If the answer is “yes†the tool fields are auto populated, the Shared
checkbox is auto checked and I fill the Job fields. One new job record is
created.
-If the answer is “no†the textbox or combobox is cleared and no action is
taken.

II) If the tool is not found in the Tools table, without having any question
from a msgbox, I fill the data for the tool and job. One new tool record and
one new job record are created.

The above required operation is not similar but I should say opposite to
what is performed usually by a Not In List Event. It seems to me that is very
convenient and I’m surprised I have never seen anywhere an application using
such a way.
Trying to work out on this I’ve made the query

SELECT Jobs.fkToolID, Jobs. JobID, Jobs.JobInfo, Jobs.StartDate,
Jobs.EndDate, Tools.ToolID, Tools.ToolName, Tools.Supplier, Tools.Shared
FROM Tools INNER JOIN Jobs ON Tools.ToolID=Jobs.fkToolID;

When I enter data in the query in table view everything works OK, but I am
trying for days to do it on a form with no success.

Does any expert on Access knows if it is possible and how?

Thanks for your response
GL

 

[Douglas J. Steele]

It's certainly doable.

Use a combo box, with Limit To List set to True.

As you're typing the name of a tool into the box, the combo box will show
you the first entry that matches what you've already typed. If you find the
tool you want, you can put your code into the combo box's AfterUpdate event
to do the prompting you want.

If the tool isn't found, the NotInList event will fire. Put code there to
add the tool to tblTools (while most of the examples show prompting the user
whether or not they want to add the entry, that's not required)

 

[Guest]

I have made a two column combobox with fkToolID as control source and the query
SELECT DISTINCT Jobs.fkToolID, Tools.ToolName FROM Tools INNER JOIN
ON Tools.ToolID = Jobs.fkToolID
as row source and I used the code below for the combo not in list event
Private Sub cboToolName_NotInList(NewData As String, Response As Integer)
Dim sMsg As String
Dim sTitle As String
Dim iAnswer As Integer
sMsg = NewData & " is not in list. Add it to table?"
sTitle = "Confirm Addition"
iAnswer = MsgBox(sMsg, vbYesNo + vbQuestion, sTitle)
If iAnswer = vbYes Then 'If answers YES to add to table
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Tools", dbOpenDynaset)
With rs
.AddNew
![ToolName] = NewData
.Update 'place values into table

End With
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
Else
Response = acDataErrContinue
End If

End Sub

Then when I select a tool name from the combobox list I have the Supplier
field populated and I can fill the rest of field.
When I type a new name in the combobox then the problems start.
After I have answered “Yes†to the “add in table†question I get an error
message that supplier cannot have a null value. Back to the Tools table I set
the required properties to “no†and I open the form again typing a name not
in list. I answer “yes†twice to the “add in list†question and then I get a
duplicate value error message probably because Access had tried to store the
same tool name in two records.
Do you see what is wrong?
How can I keep the required properties to “yes�
How can I can get rid of the Access message (not the one included in the
above code) that asks if I want to add an item to the list?
How can I escape from the combo box and fill the rest of the fields?


Thank you

 

[Douglas J Steele]

The Control Source of the combo box should simply be SELECT ToolID, ToolName
FROM Tools

Your query is hiding data: any tool that isn't yet assigned to a job won't
show up.

Afraid I don't understand your other questions:

How can I keep the required properties to "yes"?

Required properties where? If you mean how can you make it mandatory that
you have a Supplier in the Tool table, the issue is that you need to know
the Supplier when you try to add the new Tool to the Tools table. If you
don't know the Supplier at that point, you're going to have to prompt the
user to let you know who it is.

How can I can get rid of the Access message (not the one included in the
above code) that asks if I want to add an item to the list?

What's the message, and when does it appear?

How can I escape from the combo box and fill the rest of the fields?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have made a two column combobox with fkToolID as control source and the query
SELECT DISTINCT Jobs.fkToolID, Tools.ToolName FROM Tools INNER JOIN
ON Tools.ToolID = Jobs.fkToolID
as row source and I used the code below for the combo not in list event
Private Sub cboToolName_NotInList(NewData As String, Response As Integer)
Dim sMsg As String
Dim sTitle As String
Dim iAnswer As Integer
sMsg = NewData & " is not in list. Add it to table?"
sTitle = "Confirm Addition"
iAnswer = MsgBox(sMsg, vbYesNo + vbQuestion, sTitle)
If iAnswer = vbYes Then 'If answers YES to add to table
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Tools", dbOpenDynaset)
With rs
.AddNew
![ToolName] = NewData
.Update 'place values into table

End With
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
Else
Response = acDataErrContinue
End If

End Sub

Then when I select a tool name from the combobox list I have the Supplier
field populated and I can fill the rest of field.
When I type a new name in the combobox then the problems start.
After I have answered "Yes" to the "add in table" question I get an error
message that supplier cannot have a null value. Back to the Tools table I set
the required properties to "no" and I open the form again typing a name not
in list. I answer "yes" twice to the "add in list" question and then I get a
duplicate value error message probably because Access had tried to store the
same tool name in two records.
Do you see what is wrong?
How can I keep the required properties to "yes"?
How can I can get rid of the Access message (not the one included in the
above code) that asks if I want to add an item to the list?
How can I escape from the combo box and fill the rest of the fields?


Thank you

It's certainly doable.

Use a combo box, with Limit To List set to True.

As you're typing the name of a tool into the box, the combo box will show
you the first entry that matches what you've already typed. If you find the
tool you want, you can put your code into the combo box's AfterUpdate event
to do the prompting you want.

If the tool isn't found, the NotInList event will fire. Put code there to
add the tool to tblTools (while most of the examples show prompting the user
whether or not they want to add the entry, that's not required)