Two Questions on Forms for Access Database Project

[Sean Massey]

Good Morning.

I just picked up a project building an Access Database for a real estate
company to track the signs they hang in front of properties and the
lockboxes they place on homes. I'm usually pretty good at building the
database structure, but I am not very good at building and programming
the UI.

I have two questions that I hope readers on this newsgroup can help me with.

The first question has to do with using multiple forms. I want to
create a master form that allows the user to view active accounts. I
want the user to be able to access the form to create work orders by
clicking on a button, but I don't want them to have to re-enter account
information when that new form opens. How do I pass data between the forms?

My second question has to do with creating new records. The real estate
company usually tracks their active properties by the PIN number they're
assigned. There is a limited number of PINs, and they are recycled. Is
there a way to program the database so that when a new account is
created, it will search the database to check to see if the PIN# is
attached to an active property??

Thank you.

Sean

 

[Steve Schapel]

Sean,

The first question has to do with using multiple forms. I want to
create a master form that allows the user to view active accounts. I
want the user to be able to access the form to create work orders by
clicking on a button, but I don't want them to have to re-enter account
information when that new form opens. How do I pass data between the
forms?

Normally, "passing data between forms" is neither necessary or
desirable, and would possibly indicate a flaw in the table design.

In your example, if there is a relationship between the Accounts and the
Work Orders (based possibly on a field in common such as
AccountNumber?), then you can base your Work Order form on a Query that
includes the Accounts table, so the account information will be
available on the Work Order form.

My second question has to do with creating new records. The real estate
company usually tracks their active properties by the PIN number they're
assigned. There is a limited number of PINs, and they are recycled. Is
there a way to program the database so that when a new account is
created, it will search the database to check to see if the PIN# is
attached to an active property??

Query again. Assuming you have a Properties table, and a way of
determining which properties are active, and a field in this table where
the PIN number is recorded. And assuming you have another table which
contains a complete listing of all available PIN numbers. Well, then
you can make a query to give you all unallocated PINs. Just shooting in
the dark here, but maybe the query would be something along these lines...

SELECT PINs.PINnumber
FROM PINs LEFT JOIN Properties
ON PINs.PINnumber = Properties.PINnumber
WHERE Properties.Active = True AND Properties.PINnumber Is Null

 

[kingston via AccessMonster.com]

To set a control in a form equal to a control in another form:

[Forms]![Form1]![Control] = [Forms]![Form2]![Control]

To base a form's data on the value of a control in another form, create a
query where the criteria for the field looks like:

[Forms]![Form1]![Control]

To find an unmatched record, create a query and use the Unmatched Query
wizard. Or simply create a query and add the main table (PIN #) and the
secondary table (PIN # assignments). Join the two fields using a one to many
join. Add the fields from both tables and for the secondary table's field,
use the criteria Is Null.

 

[Sean Massey]

Sean,



Normally, "passing data between forms" is neither necessary or
desirable, and would possibly indicate a flaw in the table design.

I think I miscommunicated here. What I mean is that when the user of
the database opens the subform to create a work order, the pertinent
information from the account they were working on (such as account
number) won't have to be reentered into the form.

In your example, if there is a relationship between the Accounts and the
Work Orders (based possibly on a field in common such as
AccountNumber?), then you can base your Work Order form on a Query that
includes the Accounts table, so the account information will be
available on the Work Order form.


Query again. Assuming you have a Properties table, and a way of
determining which properties are active, and a field in this table where
the PIN number is recorded. And assuming you have another table which
contains a complete listing of all available PIN numbers. Well, then
you can make a query to give you all unallocated PINs. Just shooting in
the dark here, but maybe the query would be something along these lines...

SELECT PINs.PINnumber
FROM PINs LEFT JOIN Properties
ON PINs.PINnumber = Properties.PINnumber
WHERE Properties.Active = True AND Properties.PINnumber Is Null

Thank you. That is probably an easier way than I was thinking.

 

[Sean Massey]

I think I miscommunicated here. What I mean is that when the user of
the database opens the subform to create a work order, the pertinent
information from the account they were working on (such as account
number) won't have to be reentered into the form.

Nevermind. I figured this part out. It was a lot simpler than I thought.

 

[Steve Schapel]

Sean,

I see. Now you have used the word "subform". This is a good idea...
place the Work Order form onto the Account form as a subform. The Link
Master Fields and Link Child Fileds properties of the subform will be
set to the field in common, i.e. the linking field between them. That
way, the account number for the work orders will be automatically
maintained. Please let us know if you need more help with this, but if
so, please give further information about the tables and the
relationship between them.

 

[Sean Massey]

Sean,

I see. Now you have used the word "subform". This is a good idea...
place the Work Order form onto the Account form as a subform. The Link
Master Fields and Link Child Fileds properties of the subform will be
set to the field in common, i.e. the linking field between them. That
way, the account number for the work orders will be automatically
maintained. Please let us know if you need more help with this, but if
so, please give further information about the tables and the
relationship between them.

Thank you, Steve. It works like a charm.