"Subtracting" from query?

[Design by Sue]

I have a list of Company Names in the CompanyTbl., sush as AAAA, BBBB, CCCC,
DDDD, EEEE, FFFF. The CompanyPartTbl keeps track of a PartNumber and the
Companies that use that part. For example, PartNumber 12345 can be use be
Companies AAAA, CCCC, and DDDD. I have a continuous form as a subform with a
combo box that uses the CompanyTbl as the row source, so the user can assign
the companies to a given part. I would like to create code or a query for
the row source that bases the next record on the companies not already
assigned to a part number. So in my example, the fourth record would limit
the choices in the combo box to BBBB, EEEE, and FFFF.

I have been able to create a query which displays the companies that use the
part number, (in my example, shows AAAA, CCCC, and DDDD.) and am wondering if
there is a way to base another query on the first which gives the remaining
names. Basically all records in the CompanyTbl minus the query results. Or
is there another way to accomplish this.

I am not on the computer where I am doing this work so I can't post my query
tonight, but wanted to see if anyone could reply based on my description.

 

[Armen Stein]

I have a list of Company Names in the CompanyTbl., sush as AAAA, BBBB, CCCC,
DDDD, EEEE, FFFF. The CompanyPartTbl keeps track of a PartNumber and the
Companies that use that part. For example, PartNumber 12345 can be use be
Companies AAAA, CCCC, and DDDD. I have a continuous form as a subform with a
combo box that uses the CompanyTbl as the row source, so the user can assign
the companies to a given part. I would like to create code or a query for
the row source that bases the next record on the companies not already
assigned to a part number. So in my example, the fourth record would limit
the choices in the combo box to BBBB, EEEE, and FFFF.

I have been able to create a query which displays the companies that use the
part number, (in my example, shows AAAA, CCCC, and DDDD.) and am wondering if
there is a way to base another query on the first which gives the remaining
names. Basically all records in the CompanyTbl minus the query results. Or
is there another way to accomplish this.

I am not on the computer where I am doing this work so I can't post my query
tonight, but wanted to see if anyone could reply based on my description.

This can't really be done directly, because the RowSource is used for
every row in your continous form. So if you did set it to only
unassigned companies, the existing rows above would also show only
unassiged companies - so depending on whether you are showing the
bound column, the current values would either disappear or at least
not appear in the drop down list. Neither situation would be
desirable.

If you just want to prevent a company from being assigned twice, you
can add an Index with unique values to prevent a duplicate record from
being saved. It doesn't save your user any time in selecting new
values though.

Another way is to not allow a new record at the bottom of your subform
(turn off Allow Additions), but instead create a New button that pops
up a dialog form that prompts for one new record. Then you can
control the RowSource to contain just Companies that are not already
assigned. This is a "missing records" query - use the query design
wizard to create an example. Basically, you join the two tables
together, with the arrow pointing to the side you want to check for
missing records, then set your criteria so that you return only Nulls
from that side.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Design by Sue]

Thanks for your reply. Good point on the continuous form - I can see that
now.

Preventing the choice through Indexing (which I do have set) causes problems
that I am trying to avoid. for example, when the use selects a duplicate,
even though I have programmed a custom message box, gets the Access message
"creates duplicates" and then the form sort of gets locked up because unless
the user corrects his entry the Access message keeps reappearing - to the
point of real annoyance. (Yes hitting the escape key clears the problem but
the end user ofthis program isn't going to know that and shouldn't have to).

If all else fails, your suggetion of a dialog form would work, but I would
prefer to not have to break the simplicity of the form as it is.

Thanks you
Sue

 

[Armen Stein]

Thanks for your reply. Good point on the continuous form - I can see that
now.

Preventing the choice through Indexing (which I do have set) causes problems
that I am trying to avoid. for example, when the use selects a duplicate,
even though I have programmed a custom message box, gets the Access message
"creates duplicates" and then the form sort of gets locked up because unless
the user corrects his entry the Access message keeps reappearing - to the
point of real annoyance. (Yes hitting the escape key clears the problem but
the end user ofthis program isn't going to know that and shouldn't have to).

If all else fails, your suggetion of a dialog form would work, but I would
prefer to not have to break the simplicity of the form as it is.

Thanks you
Sue

Hi Sue,

You can trap for the duplicate error using a bit of code in the Form's
On Error event, like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
MsgBox "This is a duplicate record. Please correct your entry."
Me.Undo
Response = acDataErrContinue
End If

End Sub

Note that Me.Undo will undo the changes to the form so that the user
can try again.

You can research other error codes to trap for also.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Design by Sue]

I created a select query that showed the Company names that are in use and
then used a left join to remove them from the complete list available and
with a me.refresh on the afterupdate of the fields I got what I wanted to do
working. Your error trapping input will be helpful in another location
though.

Thanks

Sue