Perform a query using a field in a form as a criteria

[ember]

Hello everyone,

I want to create a report where for my orders, where there can be more
than one order per supplier in that same report. however, instead of
prompting for the supplier name, i wish that the query selects as
criteria the supplier name wich is active in the Supplier field in the
orders form. also i wold like the query to usse also as a criteria the
that the order date is today (in the query criteria in the "Order Date"
field i've inserted "Date()" and it seems to work) but i can't find a
way for the query to find the "Supplier" criteria in the "Supplier"
field in the form. what should i do, can anyone help me?





Thanks in advance,
ember

 

[Tom Wickerath]

Hi Ember,

Use: =[Forms]![FormName]![Fieldname]

as the criteria for your Supplier field. Example:

=[Forms]![frmOrders]![txtSupplier]

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello everyone,

I want to create a report where for my orders, where there can be more
than one order per supplier in that same report. however, instead of
prompting for the supplier name, i wish that the query selects as
criteria the supplier name wich is active in the Supplier field in the
orders formF. also i wold like the query to usse also as a criteria the
that the order date is today (in the query criteria in the "Order Date"
field i've inserted "Date()" and it seems to work) but i can't find a
way for the query to find the "Supplier" criteria in the "Supplier"
field in the form. what should i do, can anyone help me?

Thanks in advance,
ember

 

[ember]

Hi Ember,

Use: =[Forms]![FormName]![Fieldname]

as the criteria for your Supplier field. Example:

=[Forms]![frmOrders]![txtSupplier]

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello everyone,

I want to create a report where for my orders, where there can be more
than one order per supplier in that same report. however, instead of
prompting for the supplier name, i wish that the query selects as
criteria the supplier name wich is active in the Supplier field in the
orders formF. also i wold like the query to usse also as a criteria the
that the order date is today (in the query criteria in the "Order Date"
field i've inserted "Date()" and it seems to work) but i can't find a
way for the query to find the "Supplier" criteria in the "Supplier"
field in the form. what should i do, can anyone help me?

Thanks in advance,
ember

Tom, thanks, i've already tried that!

but now i have a doubt:

Should i write: "=[Forms]![Order]![Supplier]",

or should i write: "=[Forms]![frmOrder]![txtSupplier]" ??

because i've been using the first way, i'm a newbie using access, so
don't hit me if it's a simple mistakes

 

[Van T. Dinh]

Use the name of the TextBox Control on your Form.

Open you Form in DesignView, click the TextBox to select it, open the
Properties window (of the TextBox), select the "Other" tab and the Control
Name should be the first row in the tab.

Also, please note that a Forms doesn't have Fields, strictly speaking . A
Form has Controls, i.e. display elements like TextBox, ComboBox, etc ...
These Controls can be bound to Fields of the RecordSource of the Form. Thus
Controls and Fields are 2 distinct entities. When you use the bang !
operator, you are referring to a particular Control in the default Controls
Collection of the Form.

 

[ember]

hi,

thanks for the correction.

i've followed your advice, but now it's got an error that displays
"Type Mismatch".

i've also used quotes, but the error remains.

What can be the fault?

The form is based on a query of the tables, in wich the data type for
the control i'm querying is text.

 

[Van T. Dinh]

I am guessing that you have a tblCustomer and a tblOrder in which there is a
ForeignKey linking back to the tblCustomer.

Post the detail of this Field, i.e. Field Type, Field Size. Also post the
details of the corresponding Field in the tblCustomer.

Post also the SQL String of your Query.

 

[Tom Wickerath]

Hi Ember,

or should i write: "=[Forms]![frmOrder]![txtSupplier]" ??

Only if the name of your form happens to be "frmOrder" and the name of the
control on your form happens to be "txtSupplier". I was just giving you an
example. You need to fill in the correct names for your form and textbox
control. The first part, =[Forms]!, will always be the same. Make sure that
you use the plural "Forms" in the square brackets, not the singular "Form".
This way, you are referring to the Forms collection. (There is no Form
collection).

Thanks Van for helping out. I had called it an evening just before Ember
wrote back.

Your reply to Van included:

i've followed your advice, but now it's got an error that displays "Type Mismatch".

Type Mismatch errors can occur if, for example, you supply a text criteria
to a numeric field, or a numeric criteria to a text field. Is your supplier
control on your form a textbox, or could it possibly be a combo box or list
box?

As Van indicated, please supply more details. To post the SQL string of your
query, open your query in design view. Click on View > SQL View. Copy the SQL
(Structured Query Language) statement by selecting it with your mouse and
pressing Ctrl C. Paste it, using Ctrl V, into a reply.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?", which should be visible at the bottom
right corner of your screen.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, thanks, i've already tried that!

but now i have a doubt:

Should i write: "=[Forms]![Order]![Supplier]",

or should i write: "=[Forms]![frmOrder]![txtSupplier]" ??

because i've been using the first way, i'm a newbie using access, so
don't hit me if it's a simple mistakes

 

[Tom Wickerath]

Disregard the request to answer Yes in my last paragraph. This only applies
if you were using the Microsoft web portal to post your question, which I see
you were not using.

Tom
__________________________________________

Hi Ember,

or should i write: "=[Forms]![frmOrder]![txtSupplier]" ??

Only if the name of your form happens to be "frmOrder" and the name of the
control on your form happens to be "txtSupplier". I was just giving you an
example. You need to fill in the correct names for your form and textbox
control. The first part, =[Forms]!, will always be the same. Make sure that
you use the plural "Forms" in the square brackets, not the singular "Form".
This way, you are referring to the Forms collection. (There is no Form
collection).

Thanks Van for helping out. I had called it an evening just before Ember
wrote back.

Your reply to Van included:

i've followed your advice, but now it's got an error that displays "Type Mismatch".

Type Mismatch errors can occur if, for example, you supply a text criteria
to a numeric field, or a numeric criteria to a text field. Is your supplier
control on your form a textbox, or could it possibly be a combo box or list
box?

As Van indicated, please supply more details. To post the SQL string of your
query, open your query in design view. Click on View > SQL View. Copy the SQL
(Structured Query Language) statement by selecting it with your mouse and
pressing Ctrl C. Paste it, using Ctrl V, into a reply.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?", which should be visible at the bottom
right corner of your screen.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom, thanks, i've already tried that!

but now i have a doubt:

Should i write: "=[Forms]![Order]![Supplier]",

or should i write: "=[Forms]![frmOrder]![txtSupplier]" ??

because i've been using the first way, i'm a newbie using access, so
don't hit me if it's a simple mistakes

 

[ember]

Hi,


The Supplier table (called "Fornecedor") isn't directly connected to
the Order ( called "Guia") Table.
It is connected to _an intermediate table called Contactos (the
contact persons inside the supplier), because the order is created
through the contact,



So, I have a "Supplier (One) to Contactos (Many) relation, and a
Contactos (One) to Guia (Many) Relation.


Here is the SQL string for the query i'm using in the form:




SELECT Guia.GuiaID, Guia.[Nº Reparação], Reparacao.[Serial Nr],
Equipamento.Modelo, Reparacao.Motivo, Reparacao.[Descricao da Avaria],
Guia.[Data de Envio], Reparacao.[Data de Devolução], Guia.[Tipo de
Transporte], Guia.[Contacto de Fornecedor], Fornecedor.[Nome de
Fornecedor], Contacto.[Nº Telefone], Contacto.[E-Mail],
Fornecedor.Endereço, Fornecedor.[Código Postal], Fornecedor.[Nº
Contribuinte], [Tipo de Transporte].Matrícula



FROM [Tipo de Transporte] INNER JOIN ((Equipamento INNER JOIN Reparacao
ON Equipamento.[Nº Serie] = Reparacao.[Serial Nr]) INNER JOIN
(Fornecedor INNER JOIN (Contacto INNER JOIN Guia ON Contacto.ContactoID
= Guia.[Contacto de Fornecedor]) ON Fornecedor.FornecedorID =
Contacto.Empresa) ON Reparacao.RepairID = Guia.[Nº Reparação]) ON [Tipo
de Transporte].TransportID = Guia.[Tipo de Transporte];




Being "Fornecedor.[Nome de Fornecedor]" the field wich is connected to
the control I want to use as criteria.


P.S. - The Supplier control is in a text box, and it is formatted as
text.

Thanks all for your help,

ember

 

[Tom Wickerath]

Hi Ember,

Okay, I think I have pieced everything together, based on the SQL statement
you provided. There appears to be (6)

tables involved in this query:

Contacto (Contact people within a supplier)
Equipamento
Fornecedor (Suppliers)
Guia (Orders)
Reparacao
Tipo de Transporte


In addition to the relationships you indicated, I believe the following
relationships are present:

Tipo de Transporte ---> Guia (1:M)
Equipamento ---> Reparacao (1:M) and
Reparacao ---> Guia (1:M)

Is all of this correct?

The Data de Envio field in the Guia table sounds like it might translate to
a date type field(?). Is this the "Order

Date" field that you mentioned in your first message, where you set the
criteria = Date()?

Being "Fornecedor.[Nome de Fornecedor]" the field wich is
connected to the control I want to use as criteria.
and

The Supplier control is in a text box...

If I understand this correctly, the name of the control on the form is
"Supplier" (without the quotes), and it's

Control Source is the Nome de Fornecedor field from the Fornecedor table.
Is this correct? I'm still not sure what the

name of the form is, so I am going to use "frmOrders" as the name. You'll
need to make the appropriate substitutions in

the SQL statement shown below, assuming that you likely named your form
something different. I assume you know that

this form must be open, with a value displayed in the Supplier field, in
order for the query criteria to work properly.

If this form is closed, you will receive a parameter prompt when you try to
run the query.

The Tipo de Transporte and N Reparao foreign key fields in the Guia table
must have values entered that match the

primary key values in the TransportID field of Tipo de Transporte and the
RepairID PK in the Reparacao table, since

there are INNER JOIN relations between the tables. My advice is to start
with a query that has no criteria at all. Make

sure it returns the number of records that you expect it to return. Then
apply the Date() criteria that you mentioned

previously. Again, verify that the query returns the expected number of
records. You might even want to set your system

clock to an earlier date, and test the query again, just to make sure that
it returns the correct number of records in

each case. Once those tests pass okay, you're ready to apply the Supplier
criteria as shown in the form.

In the SQL statement that follows, the name of the form and the name of the
control on the form is included in the

first line and in the last line. I recommend copying this SQL statement to
NotePad first, save it, and then copy it to

the SQL View of a new query. The reason I say this is that I had some
trouble with the SQL statement that I copied from

your last post, which I think might have included some non-printing
characters. Saving the text in NotePad should

eliminate any non-printing characters that might cause you grief.



PARAMETERS [Forms]![frmOrders]![Supplier] Text ( 255 );

SELECT Guia.GuiaID, Guia.[N Reparao], Reparacao.[Serial Nr],
Equipamento.Modelo, Reparacao.Motivo,
Reparacao.[Descricao da Avaria], Guia.[Data de Envio],
Reparacao.[Data de Devoluo], Guia.[Tipo de Transporte],
Guia.[Contacto de Fornecedor], Fornecedor.[Nome de Fornecedor],
Contacto.[N Telefone], Contacto.[E-Mail], Fornecedor.Endereo,
Fornecedor.[Cdigo Postal], Fornecedor.[N Contribuinte],
[Tipo de Transporte].Matrcula

FROM

[Tipo de Transporte] INNER JOIN ((Equipamento
INNER JOIN Reparacao
ON Equipamento.[N Serie] = Reparacao.[Serial Nr])
INNER JOIN (Fornecedor INNER JOIN (Contacto INNER JOIN Guia ON
Contacto.ContactoID = Guia.[Contacto de Fornecedor])
ON Fornecedor.FornecedorID = Contacto.Empresa)
ON Reparacao.RepairID = Guia.[N Reparao])
ON [Tipo de Transporte].TransportID = Guia.[Tipo de Transporte]

WHERE
(((Guia.[Data de Envio])=Date())
AND
((Fornecedor.[Nome de Fornecedor])=[Forms]![frmOrders]![Supplier]));



Good Luck, and please let me know if this works for you.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html

__________________________________________

:

Hi,


The Supplier table (called "Fornecedor") isn't directly connected to
the Order ( called "Guia") Table.
It is connected to _an intermediate table called Contactos (the
contact persons inside the supplier), because the order is created
through the contact,



So, I have a "Supplier (One) to Contactos (Many) relation, and a
Contactos (One) to Guia (Many) Relation.


Here is the SQL string for the query i'm using in the form:




SELECT Guia.GuiaID, Guia.[N Reparao],
Reparacao.[Serial Nr],
Equipamento.Modelo,
Reparacao.Motivo, Reparacao.[Descricao da Avaria],
Guia.[Data de Envio],
Reparacao.[Data de Devoluo],
Guia.[Tipo de Transporte], Guia.[Contacto de Fornecedor],
Fornecedor.[Nome de Fornecedor],
Contacto.[N Telefone], Contacto.[E-Mail],
Fornecedor.Endereo, Fornecedor.[Cdigo Postal],
Fornecedor.[N Contribuinte],
[Tipo de Transporte].Matrcula

FROM [Tipo de Transporte]
INNER JOIN ((Equipamento INNER JOIN Reparacao

ON Equipamento.[N Serie] = Reparacao.[Serial Nr])

INNER JOIN (Fornecedor INNER JOIN (Contacto INNER JOIN

Guia ON Contacto.ContactoID = Guia.[Contacto de Fornecedor])
ON Fornecedor.FornecedorID = Contacto.Empresa)
ON Reparacao.RepairID = Guia.[N Reparao])
ON [Tipo de Transporte].TransportID = Guia.[Tipo de Transporte];



Being "Fornecedor.[Nome de Fornecedor]" the field wich is connected to
the control I want to use as criteria.


P.S. - The Supplier control is in a text box, and it is formatted as
text.

Thanks all for your help,

ember

 

[Tom Wickerath]

PS. Make sure to add the SQL statement I showed to a new query, instead of
replacing the SQL statement for an existing query. The reason I say this is
that you may have a parameter hiding under Query > Parameters (in query
design view) that is incorrect and causing the type mismatch error you
reported.

Also, sorry for all the line breaks in my last post. I composed the message
using NotePad, then copied it & pasted it into a reply. The line breaks were
not visible when I was editing the message before sending it.

Tom

 

[ember]

Thanks Tom!!

It finally worked!

sorry for all the bother and sorry my access teacher as well for being
so lousy... lol

Thanks once again,
ember

 

[Tom Wickerath]

Hi Ember,

You're welcome. I'm glad to hear that you got it working. Thank You for
letting me know.


Tom
___________________________________________

:

Thanks Tom!!

It finally worked!

sorry for all the bother and sorry my access teacher as well for being
so lousy... lol

Thanks once again,
ember