sql query with user interface to select

  • Thread starter Thread starter GDL
  • Start date Start date
G

GDL

hi, i want to have the possibility to choose a different "company name" with
a select window.
i dont know how ? can some help me

i want to select a different with a drop down list in access2000

SELECT T_Employer.Societe, T_Salarié.nom, T_Salarié.prenom,
L_inscriptions.Formation, T_Employer.Societe
FROM (T_Employer INNER JOIN T_Salarié ON T_Employer.ID_Employeur =
T_Salarié.ID_Employeur) INNER JOIN L_inscriptions ON T_Salarié.ID_Salarié =
L_inscriptions.Id_Salarié
WHERE (((T_Employer.Societe)="Company name"));
 
Salut,

I'm not exactly sure what you're trying to do, but if you are looking to
select a company name from a combo box and then see some results in your
form based on that company name, you can make the data source of your form
dependant upon the combo box.

For example, a simple form contains text boxes for CompanyName, FirstName,
LastName. At the very top of the form is a combo box which says "look up a
company". You select a company from the combo box list (assuming you know
how to put the data in the combo box). You make an event AfterUpdate with
the following code:

Me.Requery

The Record Source for MySimpleForm is something like this:

SELECT Company.Name, Company.FirstName, Company.LastName FROM Company
WHERE (((Company.Name)=[Forms]![MySimpleForm]![MyComboBoxAtTheTop]));

When the form first loads, the form is blank - ready to enter a new record
or to lookup a record.

Hope this helps.

Linda
 
ok, thanks, but this is not exactly what i want (sorry for me bad english,
i'm Dutch)

How can I take the value of a field with me to make a query.
I added a button to the form to run a query.

in this query I want to use the information of some of the fields of the
current Form

Form fields :
Fcompany = "Company name"
FCompanyAdress = "adress"

when I press the button to run the query i want to use the information of
the current record in the Form

SELECT T_Employer.Societe, T_Salarié.nom, T_Salarié.prenom,
L_inscriptions.Formation, T_Employer.Societe
FROM (T_Employer INNER JOIN T_Salarié ON T_Employer.ID_Employeur =
T_Salarié.ID_Employeur) INNER JOIN L_inscriptions ON T_Salarié.ID_Salarié
= L_inscriptions.Id_Salarié
WHERE (((T_Employer.Societe)="Company name"));
____________


Linda Burnside said:
Salut,

I'm not exactly sure what you're trying to do, but if you are looking to
select a company name from a combo box and then see some results in your
form based on that company name, you can make the data source of your form
dependant upon the combo box.

For example, a simple form contains text boxes for CompanyName, FirstName,
LastName. At the very top of the form is a combo box which says "look up
a company". You select a company from the combo box list (assuming you
know how to put the data in the combo box). You make an event AfterUpdate
with the following code:

Me.Requery

The Record Source for MySimpleForm is something like this:

SELECT Company.Name, Company.FirstName, Company.LastName FROM Company
WHERE (((Company.Name)=[Forms]![MySimpleForm]![MyComboBoxAtTheTop]));

When the form first loads, the form is blank - ready to enter a new record
or to lookup a record.

Hope this helps.

Linda

GDL said:
hi, i want to have the possibility to choose a different "company name"
with a select window.
i dont know how ? can some help me

i want to select a different with a drop down list in access2000

SELECT T_Employer.Societe, T_Salarié.nom, T_Salarié.prenom,
L_inscriptions.Formation, T_Employer.Societe
FROM (T_Employer INNER JOIN T_Salarié ON T_Employer.ID_Employeur =
T_Salarié.ID_Employeur) INNER JOIN L_inscriptions ON T_Salarié.ID_Salarié
= L_inscriptions.Id_Salarié
WHERE (((T_Employer.Societe)="Company name"));
Click to expand...
Click to expand...
 
GDL said:
ok, thanks, but this is not exactly what i want (sorry for me bad english,
i'm Dutch)
Click to expand...

A friend of mine has a bumper sticker which reads
"If it ain't Dutch, it ain't mutch!"
How can I take the value of a field with me to make a query.
I added a button to the form to run a query.

in this query I want to use the information of some of the fields of the
current Form

Form fields :
Fcompany = "Company name"
FCompanyAdress = "adress"
Click to expand...

I didn't see any reference to "adress" in your Query, so I ignored it
(but it shouldn't be difficult to add).

I defined the following sample Tables:

[T_Employer]
ID_Employeur Societe
------------ --------------
138259760 Honeywell-Bull
946901361 Citroen
1159198283 Disney

[T_Salarié]
ID_Salarié nom prenom ID_Employeur
----------- ----------- ------ ------------
-1494118340 Clémenceau Georges 946901361
-991921913 deGaulle Charles 138259760
-172681506 Disney Walt 1159198283
1124876941 Mouse Mickey 1159198283

[L_inscriptions]
L_inscriptionsID Id_Salarié Formation
---------------- ----------- ---------
-1890549885 -1494118340 yyy
-1124983447 1124876941 mmm
360061960 -172681506 zzz
1865572554 -991921913 xxx

I defined Query [Q_Salarié] using the following SQL (similar to your Query):

SELECT T_Employer.ID_Employeur, T_Salarié.nom,
T_Salarié.prenom, L_inscriptions.Formation,
T_Employer.Societe
FROM (T_Employer INNER JOIN T_Salarié
ON T_Employer.ID_Employeur = T_Salarié.ID_Employeur)
INNER JOIN L_inscriptions
ON T_Salarié.ID_Salarié = L_inscriptions.Id_Salarié
WHERE (((T_Employer.ID_Employeur)=[FCompany]));

For example, if [FCompany] = 1159198283, this Query, in Datasheet View,
looks like this:

ID_Employeur nom prenom Formation Societe
------------ ------ ------ --------- -------
1159198283 Disney Walt zzz Disney
1159198283 Mouse Mickey mmm Disney

On [Form1] I placed the following two controls (among others):

In 1st list box, [FCompany], set properties as follows:

Name: FCompany
Row Source: T_Employer
Column Count: 2
Column Widths: 0;1
Bound Column: 1
After Update: M_Requery

In 2nd list box, [lbxLast], set properties as follows:

Name: lbxLast
Row Source: Q_Salarié
Column Count: 2
Column Widths: 0";1"
Bound Column: 1
Locked: Yes

Define Macro [M_Requery] to contain the following action:

Requery lbxLast

Clicking on a name in control [FCompany] causes the name(s) displayed by
List Box [lbxLast] to change to match the selected company name.
 
Back
Top