G
Guest
I’m hoping someone may be able to help me with a problem I’m having. I look
after a database which is used to track proposals for a company. There are
two types of proposals that are tracked and this is determined by a field
called the “ReqTypeâ€. In order to view the proposals for either type the
user selects which type they want from the main switchboard. Based on their
selection, a form will open to display all the records and the information on
them for that proposal type. I have created 3 queries for use on my form,
one for each of the proposal types and one for all records. While on this
form, the user is able to switch back and forth between the two types or
display all records by way of command buttons that change which query acts as
the record source for the form. The user is able to navigate between the
various proposals by way of a combo box. The combo box only displays the
records that pertain to the type of proposal they user has chosen. All of
this is working flawlessly.
Where I run into a snag is when a user is attempting to add a new record.
Currently if they have selected proposal type A, or All proposals and they
attempt to add another record of proposal type A, the record is added without
incident. Likewise if they attempt to add a record of proposal Type B, while
they have selected proposal type B or All proposals, the record is added
without a hitch. In either case if they attempt to add a record that already
exists, as soon as the combo box for the proposal number loses focus, they
are immediately taken to that record, thus saving them entering all the info
only to find out the record was already there. Where things fall apart is
when a user isn’t paying attention and attempts to add a new record of Type A
while the filter is set for Type B, or vice versa. Because the query acts as
the record source for the form, and the combo box for the proposal numbers
only displays those records whose ReqType matches that of the query; if a
user attempts to add a record with ReqType A to the form which is filtered to
RecType B, they aren’t given any indication that the record exists since the
proposal number doesn’t show in the list. The user is led to believe that
this is a new record and fills in all the information. When they click the
Save button after entering all the data, they are given a message saying that
they could not add the record as this would cause a duplicate value.
Is there a way to set it so when the combo box is checking to see if the
number is in the list, that it checks the “all†records query instead of the
selected ReqType and if the record is already in the table, then warn the
user by way of a message box that the record they are attempting to add
exists but has been assigned the other ReqType?
Thanks in advance.
--
Cheers
Klingon Coder
"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"
after a database which is used to track proposals for a company. There are
two types of proposals that are tracked and this is determined by a field
called the “ReqTypeâ€. In order to view the proposals for either type the
user selects which type they want from the main switchboard. Based on their
selection, a form will open to display all the records and the information on
them for that proposal type. I have created 3 queries for use on my form,
one for each of the proposal types and one for all records. While on this
form, the user is able to switch back and forth between the two types or
display all records by way of command buttons that change which query acts as
the record source for the form. The user is able to navigate between the
various proposals by way of a combo box. The combo box only displays the
records that pertain to the type of proposal they user has chosen. All of
this is working flawlessly.
Where I run into a snag is when a user is attempting to add a new record.
Currently if they have selected proposal type A, or All proposals and they
attempt to add another record of proposal type A, the record is added without
incident. Likewise if they attempt to add a record of proposal Type B, while
they have selected proposal type B or All proposals, the record is added
without a hitch. In either case if they attempt to add a record that already
exists, as soon as the combo box for the proposal number loses focus, they
are immediately taken to that record, thus saving them entering all the info
only to find out the record was already there. Where things fall apart is
when a user isn’t paying attention and attempts to add a new record of Type A
while the filter is set for Type B, or vice versa. Because the query acts as
the record source for the form, and the combo box for the proposal numbers
only displays those records whose ReqType matches that of the query; if a
user attempts to add a record with ReqType A to the form which is filtered to
RecType B, they aren’t given any indication that the record exists since the
proposal number doesn’t show in the list. The user is led to believe that
this is a new record and fills in all the information. When they click the
Save button after entering all the data, they are given a message saying that
they could not add the record as this would cause a duplicate value.
Is there a way to set it so when the combo box is checking to see if the
number is in the list, that it checks the “all†records query instead of the
selected ReqType and if the record is already in the table, then warn the
user by way of a message box that the record they are attempting to add
exists but has been assigned the other ReqType?
Thanks in advance.
--
Cheers
Klingon Coder
"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"