Hi Lin,
One big one, change lookup tables to text tables
in my Order and Invoice data tables.
What exactly is a "text table"? Never heard of the term.
Well I can no longer create a form using fields from different tables.
You should be able to, if you base your form on a query. A bound form includes a recordsource
(ie. source of records). A recordsource can be a table, or a query that draws data from one or
more tables.
In the form wizard I now get a different pop-up that
asks if I want separate forms or linked?
This indicates to me that the wizard is detecting a one-to-many relationship in your selected
data.
Also I can't use option three to bind stuff.
The third option of the combo box wizard is intended to find existing records; it is not intended
to create a combo box for data entry tasks.
The look up information is in a different table, so only
the first or top option on the Combo Box Wizard
even comes close to doing what I want.
The first two options are used to create a bound combo box for data entry. Option # 1 uses a
lookup table, which is generally preferable to option # 2, which uses a value list.
Please clarify the intent of your combo box. Is it to find an existing record in a recordset
(use the third option) or is it to assist in data entry?
I'm getting pretty tired right now, so I'm going to sign off for the next several hours.
Tom
_______________________________
Looked at you site, made some changes, One big one, change lookup tables to
text tables in my Order and Invoice data tables. Created Combo boxes in
forms to handle corrections. Now back to the original issue. I thought
after I made the changes, go ahead and create a new form. Well I can
Nolonger create a form using fields from different tables. In the form
wizard I now get a different pop-up that asks if I want seperate forms or
linked?
ALso I can't use option three to bind stuff. The look up information is in
a different table, so only the first or top option on the Combo Box Wizard
even comes close to doing what I want.
LIn
_______________________________
Hi Lin,
I'm not sure, since I tend to avoid the use of Lookup fields. See the second commandment at:
http://www.mvps.org/access/tencommandments.htm
The easiest test to run is to make a copy of your database, eliminate the lookup field, and then
see if the error is repeated. Shouldn't take more than a few minutes worth of effort to run this
experiment. Please let us know what you find. Maybe we can contribute an evil to the list!
Also, I see that you have a table or query named "A/V Order List". While the query wizard
appears to have correctly bracketed this name with the square brackets (ie. "[" and "]" ), I
recommend that you pick better names for your fields, objects (tables, queries, forms, reports,
etc.) and controls. Here are some articles that I suggest reading:
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335
List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp
Tom
_______________________________
I just thought Of something. The Vendor field in my Order Table is a "Look
Up" field. When The order is being constructed, the Vendor data is pulled
down off a Vendor List. Could this have an effect?
Lin
_______________________________
When I get to selecting "Which fields contain the values you want included in
your combo box?..." I bring over PO and Vendor fields from the Order list and
hit next and I get "Syntax error in query expression [SELECT[A/V Order
List].[Vendor]." and I can't go any further.
This is when I select the third option on the beginning Combo Box Wizard.
LIn
_______________________________
But while testing, I will enter in a PO number that I know is in
the Order Table, but it takes upon itself to change the number.
It sounds like the combo box that you are using to look up a value is bound to a field (ie. has a
control source specified). When you are using a combo box to set a value for a given record, then
you need to store the foreign key value from a parent table in the field that is specified as the
control source. However, if you are using a combo box to look up a different record, then in
general, you want to use an unbound combo box (ie. no control source). Perhaps this tutorial
will be helpful to you:
Using a Combo Box to Find a Record
http://www.access.qbuilt.com/html/find_a_record.html
Tom
________________________________
I have created a form based on two tables, one Orders the other Invoices.
The task was to enter a PO number, which is in the Order table and have the
vendor of that order populate the vendor field in the Invoice table. I did
this with a combo box with both the vendor and po involved. Look up PO in
the Order Table and bring up the associated Vendor. I have a form for
Invoice data entry and the first box is where I enter the PO number and it is
set to auto expand so I only need to enter in the first couple of numbers and
hit enter when I see the correct number. With this action, I have, through
the combo box, hey while you are in that record, show me the vendor, which it
does, and oh by the way store it in the Invoice table for this invoice
record, which it does. But while testing, I will enter in a PO number that I
know is in the Order Table, but it takes upon itself to change the number. I
can enter in PO 5502 but it displays 5522, even if I look it up on the drop
down list and highlight it.
So if we could tackle this one and come up with the reason and solution, I
will ask one other question.
Lin