Responses inline.
babs said:
Let me do some explaining
Isn't Taxemempt# something that is
It is only unique to a client AND a JOB#
Then it goes in the job table, or the quote table, or wherever you keep job
information.
When preparing a quote you would typically build a form based on the Quote
table, and select a client from a combo box that is based on the Client
table. Any Client information you wish to appear on the Quote form can
then
come along for the ride.
CORRECT
If the Taxexempt table is a duplicate of the
Quotes table except that it contains completed jobs, the better approach
would be to add a Yes/No field [Completed] to the Quotes table
Would always want BOTH. Anything in the taxexempt table is really JUST
jobs
PRIOR to creating the quotes form in the database.
Do you mean prior to creating the quotes record? In any case, if you are
creating duplicate job information in two tables, your design needs
adjusting. If you are moving information from one table to another, you are
doing something unnecessary. See below.
Any NEW even completed
jobs in the quote form will show up in the dropdown for the quotes form-
Do I
need a check box (default to YES)- to have the job# dropdown to be able to
display BOTH JOB#,taxeex from quote table and taxexempt table
The check box is for selecting either open or closed jobs, or both. See
below.
Add the new Yes/No field to the table. Check the box for some of the
records. Start a query in design view. Select all of the fields from a
table (including the new Yes/No field). In the Criteria row for that field,
enter the word True. Switch to datasheet view. You will see only the
records you have checked. Switch back to design view, and change the
criteria to False. Switch to datasheet view. You will see only jobs in
which that box is not checked. Once more to design view. Remove the
criteria. You will see all of the table fields. You can base a
CompletedJobs form on one query, and an OpenJobs form on another query. You
can build your combo box row source from the table, and view all of the
jobs.
Youc an also have a single form for viewing Open, Closed, or All. Code
behind command buttons can change the RecordSet you use.
See prior write ups to see the 3 tables I have. Quote, client, taxexempt
( when pull all three in query not updatable)
Yes the Code is attached to the after UPdate event of the combo box-
Clientid after a client is selected and the code populates the rowsource
for
the combo box job# depending on which clientid was selected.
I take it that you are selecting ClientID from a combo box (perhaps the
client name is the visible column, and ClientID is the bound column). In
any case, the combo box (cboClientID?) After Update event sets the row
source for cboJob.
Another thing to consider is that you could use a subform to display the
client's job information. In that case you would just need to go to the
client record to view all of that client's jobs.
The job# will
then show all previous jobs and their taxex# from the quoteform(based on a
query using quotetable and client table). Just to summarize - would like
to
also include on the job# drop down - old jobs that are JUst in the
taxexempt
table.
There is probably no need for a separate table for completed jobs.
You have not mentioned relationships between the tables. I have been
assuming there is a relationship between ClientID in the client table and
each of the other tables. If so, I believe you could build a query that
includes the client's records from both tables, but to what end? What do
you wish to have happen when you select a job number from the combo box. If
you want to select either an open or a closed job, it makes no sense to
store the jobs in separate tables, then try to merge the tables through use
of a query. You need access to both open and closed jobs, so you should
store them in the same table.
thanks for helping,
Barb
BruceM said:
Maybe I misunderstand something here. Isn't Taxemempt# something that is
unique to a client? If so, it should be part of the Client record. If
not,
what is it? Remember, most of us are not in your business.
When preparing a quote you would typically build a form based on the
Quote
table, and select a client from a combo box that is based on the Client
table. Any Client information you wish to appear on the Quote form can
then
come along for the ride. If the Taxexempt table is a duplicate of the
Quotes table except that it contains completed jobs, the better approach
would be to add a Yes/No field [Completed] to the Quotes table. You can
then build queries (including Row Source code) to choose open jobs,
completed jobs, or both.
In what event and on what form does the code appear? You reference
setting
the Row Source of ClientID. Is ClientID a combo box? It seems you are
setting the Row Source of cboJob. Some description of your forms,
including
their Record Sources, would help.
babs said:
This is the code for the drop down for job# I am including the
taxexempt#
because I want to set the value of a the taxex field on the form to
whatever
job#,taxex# they select in the drop down.
'This function sets the RowSource of ClientId, based on the value
selected
in ClientId
sSql = "SELECT DISTINCT Job, Taxex, Jobdesc, ClientId " & "FROM
tblQUOTEJOE
" & "WHERE ClientId = """ & Me.cboClientId & """ " & "ORDER BY Job"
Me.cbojob.RowSource = sSql
I have 3 tables
Client- client id(PK), name, address, etc.
quotes-quoteid(PK), clientid, job#,taxex,rate, etc.
taxexempt table-ID(PK), clientid, job#, taxex
The taxexempt table just holds PREVIOUSLY done jobs and their taxex #
but
would like to make them also available to the dropdown to be selected
AS
WELL
AS the taxex values in the quote table. I tried to pull all three
tables
together in a query but could only use the client and quotes table.
(that's
really why I put the taxex field in the quotes table. With ALL 3
tables
in
the query it is not updatable.
Thanks,
Barb
:
I take it that you have a combo box on a form, but I do not follow the
details. When you say "populating the dropdown" are you referring to
the
combo box row source? Why do job# and taxempt# appear in the same
combo
box? What is the taxes table? Are you copying records from one table
to
another? If so, that is at least part of the problem, as you should
not
be
doing that.
What is the purpose of your database? What real-world situation are
you
attempting to address?
As a possible answer to your question, you could try adding the word
DISTINCT after SELECT in the row source SQL. Another way of doing
this:
select the combo box, click View > Properties, click the Data tab,
click
Row
Source, click the three dots. In the query design view that appears,
click
View > Properties, and set Unique Values to Yes.
However, if your database is not set up properly, you may not get the
desired result. In that case, you will need to describe the database
structure and relationships.
I have a drop down list for job# on a form called quotes showing the
job#
and
taxexempt # based on client id. Right now I am populating the
dropdown
based
on previous entries on the existing form that enter the data into a
table
called quotes. I would like to do either of two things. 1. Add these
entries(one entry per clientid, job#,taxexempt#) into another table
called
taxex table.
Definitely want to know at least this. Have the user be able to add
clientid, job#, and taxempt# to the Taxex table and it ALSO be able
to
show
up in the dropdown as a choice as well as the records previously
input
into
the quote entry form. Not sure how to pull from two sources and only
display
once if duplicates- for a given drop down.
Thanks so much,
Barb
