Searching Record

[sys_analyst47 via AccessMonster.com]

Hi,

I just want to know something about record searching on lost event of combo
box. I have a relationship database in which my first table contain staff ID
& Staff Name then in another table contains different parameters along with
Staff ID & Staff Name my primary key is Staff ID what i want is this once a
staff select his ID in combo box on lost event of it below is the text box
which should be populated with staff name.

I am using Access 2007. please revert asap.

 

[Jeanette Cunningham]

You can set the control source of the textbox to show the staff name.
Put this in the textbox control source

=[ComboName].Column(1)

Replace ComboName with the name of your combo.
The square brackets are needed if the combo name has spaces in it.
I am assuming that the combo has StaffID for the first column and StaffName
for the second column.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[sys_analyst47 via AccessMonster.com]

You can set the control source of the textbox to show the staff name.
Put this in the textbox control source

=[ComboName].Column(1)

Replace ComboName with the name of your combo.
The square brackets are needed if the combo name has spaces in it.
I am assuming that the combo has StaffID for the first column and StaffName
for the second column.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 10 lines]

I am using Access 2007. please revert asap.

Dear Access Guru,

Well thanks for your revert but still i think may be i didn't make it clear
or whatever please check my query once again " I have one table named as
Staff ID & Staff Name & other table contains the activities of Staff i made
the form based on activity table. Now Primary key is the Staff Id in 1st
table & foreign key is activity table by using Enforce referential integrity.
Now of Activity form if a user want to enter his daily activity he uses his
ID i want that user name will automatically appear there"

SELECT Daily_Activity.[Staff ID], Staff.[Staff Name]
FROM Staff INNER JOIN Daily_Activity ON Staff.[Staff ID] = Daily_Activity.
[Staff ID]
WHERE (((Daily_Activity.[Staff ID])=[Forms]![Daily_Activity]![Staff Name])
AND ((Staff.[Staff Name])=[Forms]![Daily_Activity]![Staff ID]));


the above is the query which i made this is reflecting the correct result
like if i put ID of a staff it gives the correct name based in Master table
of Staff name which contain Staff ID & Name.

if its still not clear please note my email [email protected] reply
me i will send you the screen dumps or the database which i designed.

 

[Jeanette Cunningham]

I'm not sure I understand your question

Is this your setup?
The combo is in the header of your form.
You select the staff name from the combo.
The code in the after update event for the combo finds all the records for
that staff name.


Where is the textbox to show the staff name?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You can set the control source of the textbox to show the staff name.
Put this in the textbox control source

=[ComboName].Column(1)

Replace ComboName with the name of your combo.
The square brackets are needed if the combo name has spaces in it.
I am assuming that the combo has StaffID for the first column and
StaffName
for the second column.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

[quoted text clipped - 10 lines]

I am using Access 2007. please revert asap.

Dear Access Guru,

Well thanks for your revert but still i think may be i didn't make it
clear
or whatever please check my query once again " I have one table named as
Staff ID & Staff Name & other table contains the activities of Staff i
made
the form based on activity table. Now Primary key is the Staff Id in 1st
table & foreign key is activity table by using Enforce referential
integrity.
Now of Activity form if a user want to enter his daily activity he uses
his
ID i want that user name will automatically appear there"

SELECT Daily_Activity.[Staff ID], Staff.[Staff Name]
FROM Staff INNER JOIN Daily_Activity ON Staff.[Staff ID] = Daily_Activity.
[Staff ID]
WHERE (((Daily_Activity.[Staff ID])=[Forms]![Daily_Activity]![Staff Name])
AND ((Staff.[Staff Name])=[Forms]![Daily_Activity]![Staff ID]));


the above is the query which i made this is reflecting the correct result
like if i put ID of a staff it gives the correct name based in Master
table
of Staff name which contain Staff ID & Name.

if its still not clear please note my email [email protected]
reply
me i will send you the screen dumps or the database which i designed.

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

 

[sys_analyst47 via AccessMonster.com]

I'm not sure I understand your question

Is this your setup?
The combo is in the header of your form.
You select the staff name from the combo.
The code in the after update event for the combo finds all the records for
that staff name.

Where is the textbox to show the staff name?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
[quoted text clipped - 43 lines]

reply
me i will send you the screen dumps or the database which i designed.

Hi,

Yes combo box is first then i kept Staff name text box next to it. So when
the data pick from my staff table which is staff name this will save into my
daily activity staff name field? and you want me to add the code on after
update event what does coulmn(1) refers to ???

 

[sys_analyst47 via AccessMonster.com]

I'm not sure I understand your question

Is this your setup?
The combo is in the header of your form.
You select the staff name from the combo.
The code in the after update event for the combo finds all the records for
that staff name.

Where is the textbox to show the staff name?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
[quoted text clipped - 43 lines]

reply
me i will send you the screen dumps or the database which i designed.

Hi,

One thing which i don't understand the way you refer me to do is that just
based on combo box update event you said me to take the name. But the name of
staffs are in different table the code which you told me i think there is no
reference sort of thing which fetch record from the staff table. Is there any
way we make query & make the row source ??? Correct me if i am wrong row
source will be with which thing this will be populate and record source from
where this record is reflecting means which table and all.

 

[Jeanette Cunningham]

Now I understand what you are trying to do.
For the subform, base its record source on a query that uses both tables.
Include the field for staff name from the staff table, as well as the fields
from the activities table, and the staff name will show up on each row of
the subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australias

I'm not sure I understand your question

Is this your setup?
The combo is in the header of your form.
You select the staff name from the combo.
The code in the after update event for the combo finds all the records for
that staff name.

Where is the textbox to show the staff name?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You can set the control source of the textbox to show the staff name.
Put this in the textbox control source

[quoted text clipped - 43 lines]

reply
me i will send you the screen dumps or the database which i designed.

Hi,

One thing which i don't understand the way you refer me to do is that just
based on combo box update event you said me to take the name. But the name
of
staffs are in different table the code which you told me i think there is
no
reference sort of thing which fetch record from the staff table. Is there
any
way we make query & make the row source ??? Correct me if i am wrong row
source will be with which thing this will be populate and record source
from
where this record is reflecting means which table and all.

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

 

[sys_analyst47 via AccessMonster.com]

Now I understand what you are trying to do.
For the subform, base its record source on a query that uses both tables.
Include the field for staff name from the staff table, as well as the fields
from the activities table, and the staff name will show up on each row of
the subform.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australias
[quoted text clipped - 27 lines]

from
where this record is reflecting means which table and all.

Hi,

Dear i Dont want any subform i just want staff name in text box that's it.

 

[Jeanette Cunningham]

Where is the text box for the staff name?
In the main form?
What is the record source for the main form?
The combo will already show the staff name.
I am having trouble understanding what is the purpose of putting the staff
name in a textbox.
Without this understanding, it is very hard for me to answer your question.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Now I understand what you are trying to do.
For the subform, base its record source on a query that uses both tables.
Include the field for staff name from the staff table, as well as the
fields
from the activities table, and the staff name will show up on each row of
the subform.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australias

I'm not sure I understand your question

[quoted text clipped - 27 lines]

from
where this record is reflecting means which table and all.

Hi,

Dear i Dont want any subform i just want staff name in text box that's it.

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

 

[sys_analyst47 via AccessMonster.com]

Where is the text box for the staff name?
In the main form?
What is the record source for the main form?
The combo will already show the staff name.
I am having trouble understanding what is the purpose of putting the staff
name in a textbox.
Without this understanding, it is very hard for me to answer your question.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
[quoted text clipped - 14 lines]

Dear i Dont want any subform i just want staff name in text box that's it.

Dear,

Please accept my apologies to bother you alot. The reason for mentioning
staff name is that I use referential integrity so that no other staff can
enter record for these staff i put name against it in staff tables.
Management will only see the staff name coz nobody remember staff ID's. Well
below is detailed scenario:

Table Staff: Which contains Staff ID & Staff Name

Daily Activity Table: Staff ID (for which i put combo with list of Staff ID's)
, Staff Name, Product, time in & time out in last TAT.

Now my form is based on Daily activity table: Assume user is entering his
daily activity he selects his ID now at that point in time the field of Staff
name should populate with his name. That's the thing which i want to
implement.

Once again please accept my apologies

 

[Jeanette Cunningham]

Apologies accepted (well not really needed, many people who answer questions
here have difficulty understanding what is the problem the poster is wanting
help with).

It is easier to find the name of staff who opens this copy of the database
when the database is opened.
Once you have that info, you can store it in a table or in a hidden form.

When user wants to open the activities form, the database uses the saved
staff name to filter the activities form and uses that name to add new
records and edit existing records.

Do your users logon to the database?
Can you stop them from logging on with someone else's name?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Where is the text box for the staff name?
In the main form?
What is the record source for the main form?
The combo will already show the staff name.
I am having trouble understanding what is the purpose of putting the staff
name in a textbox.
Without this understanding, it is very hard for me to answer your
question.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Now I understand what you are trying to do.
For the subform, base its record source on a query that uses both
tables.

[quoted text clipped - 14 lines]

Dear i Dont want any subform i just want staff name in text box that's
it.

Dear,

Please accept my apologies to bother you alot. The reason for mentioning
staff name is that I use referential integrity so that no other staff can
enter record for these staff i put name against it in staff tables.
Management will only see the staff name coz nobody remember staff ID's.
Well
below is detailed scenario:

Table Staff: Which contains Staff ID & Staff Name

Daily Activity Table: Staff ID (for which i put combo with list of Staff
ID's)
, Staff Name, Product, time in & time out in last TAT.

Now my form is based on Daily activity table: Assume user is entering his
daily activity he selects his ID now at that point in time the field of
Staff
name should populate with his name. That's the thing which i want to
implement.

Once again please accept my apologies

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

 

[sys_analyst47 via AccessMonster.com]

Apologies accepted (well not really needed, many people who answer questions
here have difficulty understanding what is the problem the poster is wanting
help with).

It is easier to find the name of staff who opens this copy of the database
when the database is opened.
Once you have that info, you can store it in a table or in a hidden form.

When user wants to open the activities form, the database uses the saved
staff name to filter the activities form and uses that name to add new
records and edit existing records.

Do your users logon to the database?
Can you stop them from logging on with someone else's name?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
[quoted text clipped - 37 lines]

Once again please accept my apologies

See what i did for the sake of inter dept reporting you know daily activities
tracking. I kept tables on shared drive & provide specific forms to user as
they required like daily activity.
And its just a password protected database a common password by which users
can open it. How can we do this like Only specific users can open interface ??
? another question for you.

 

[Jeanette Cunningham]

You can have a drop down with users' names when the database opens.
Create a field to hold the password for each user in the staff table.
Users select their name and enter their password.
Your code checks that the password entered is correct.
Save the users' staffID to a table or a textbox on a hidden form.

Every time you open the activities form, use a where condition that includes
StaffID = forms!HiddenForm.txtStaffID


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Apologies accepted (well not really needed, many people who answer
questions
here have difficulty understanding what is the problem the poster is
wanting
help with).

It is easier to find the name of staff who opens this copy of the database
when the database is opened.
Once you have that info, you can store it in a table or in a hidden form.

When user wants to open the activities form, the database uses the saved
staff name to filter the activities form and uses that name to add new
records and edit existing records.

Do your users logon to the database?
Can you stop them from logging on with someone else's name?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Where is the text box for the staff name?
In the main form?

[quoted text clipped - 37 lines]

Once again please accept my apologies

See what i did for the sake of inter dept reporting you know daily
activities
tracking. I kept tables on shared drive & provide specific forms to user
as
they required like daily activity.
And its just a password protected database a common password by which
users
can open it. How can we do this like Only specific users can open
interface ??
? another question for you.

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

 

[sys_analyst47 via AccessMonster.com]

You can have a drop down with users' names when the database opens.
Create a field to hold the password for each user in the staff table.
Users select their name and enter their password.
Your code checks that the password entered is correct.
Save the users' staffID to a table or a textbox on a hidden form.

Every time you open the activities form, use a where condition that includes
StaffID = forms!HiddenForm.txtStaffID

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
[quoted text clipped - 31 lines]

interface ??
? another question for you.

but how can only that specific form will be load on the time of opening
access. Also tell me other forms are already there for usage a user can go
and open the forms what to do in this situation or any way that if a password
is correct that only the user can see the forms

 

[Jeanette Cunningham]

You can set the password as the start up form in the startup properties for
the database.

You can set other forms to open to the staffID saved on the hidden form, as
explained in my last post.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You can have a drop down with users' names when the database opens.
Create a field to hold the password for each user in the staff table.
Users select their name and enter their password.
Your code checks that the password entered is correct.
Save the users' staffID to a table or a textbox on a hidden form.

Every time you open the activities form, use a where condition that
includes
StaffID = forms!HiddenForm.txtStaffID

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Apologies accepted (well not really needed, many people who answer
questions

[quoted text clipped - 31 lines]

interface ??
? another question for you.

but how can only that specific form will be load on the time of opening
access. Also tell me other forms are already there for usage a user can go
and open the forms what to do in this situation or any way that if a
password
is correct that only the user can see the forms

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE

 

[sys_analyst47 via AccessMonster.com]

You can set the password as the start up form in the startup properties for
the database.

You can set other forms to open to the staffID saved on the hidden form, as
explained in my last post.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
[quoted text clipped - 19 lines]

password
is correct that only the user can see the forms

Alright tht's Done ... well I have another question for you Guru ... you
understand wht is Guru .. well is it possible to do mail merge from
multiple tables i mean if i want to take data from multiple tables to
generate a customer contract suppose it is of 7 pages so it require data from
3 tables of my database is it possible

 

[Jeanette Cunningham]

Thanks for the 'guru' compliment.
Mail merge is not something I have experience with.
Sorry I can't help you further.
Suggest you start a new post about your mail merge questions.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You can set the password as the start up form in the startup properties
for
the database.

You can set other forms to open to the staffID saved on the hidden form,
as
explained in my last post.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You can have a drop down with users' names when the database opens.
Create a field to hold the password for each user in the staff table.

[quoted text clipped - 19 lines]

password
is correct that only the user can see the forms

Alright tht's Done ... well I have another question for you Guru ... you
understand wht is Guru .. well is it possible to do mail merge from
multiple tables i mean if i want to take data from multiple tables to
generate a customer contract suppose it is of 7 pages so it require data
from
3 tables of my database is it possible

--
Thanks & Regards
Majid Pervaiz
Operations Analyst
Al Hilal Bank, Abu Dhabi, UAE