Access Advice

[Trevy]

I am new to access and am setting up a db for a trust account. I have a
table called listings that I want to input all the companies listed
properties and a form for those listings. I have a combo box labeled "status"
and was wondering if when I change the status to pending in the status combo
box if I can have it pull up a new form called pendings and pulling
information from the listing table to the pending form? I could then add the
new information regarding the pending. I was using 2 separate tables but
someone suggested that I only need 1. I have 2 forms setup right now but all
the information from the table is showing on both forms, I only want it to
show on the pendings form when the property goes pending. Please let me know
what you think.
Thanks

 

[Ken Snell MVP]

Yes, you can have your "pending" form open when you change the status in the
combobox to "Pending". That can be done by using the AfterUpdate event
procedure for the combobox to open the form, where the code checks the value
in the combobox and opens the form if it's "pending". However, from a
performance perspective, you might want some confirmation that the status
change is correct before immediately opening that second form.

That second form could be populated with the information from the first form
again by code. The code could read the values from first form and write them
into the second form. The code could be done by the same procedure that
opens the second form.

However, before we even head down this path, let's visit the last item you
mentioned first. Whether you need two tables or can use just one table.
Likely, one table will be the better approach, but let's learn what data
you're storing in the first table and which data in the second table. Give
us some more details, please.

 

[Trevy via AccessMonster.com]

My tables are Listings and Pendings the Listing table has the following
columns--ID, Address, City, Phase, Project, Builder, Model, Seller, Agent,
Status, Start Date, Expiration Date, Price, Beds, Baths, Garage, SQFT, Notes,
Attachments. The Pendings table has the following columns--Contact #, Year,
Outside Agent, Agent, Closed Date, Address, City, Buyer, Seller, Status, Open
Date, Pending Price, Listing Price, Attachments ,Notes.
Notes and Attachments needs to be able to have different information entered
on each form.
Could the listing form be setup so if you double click on a field it would
take you back to the listing form or table to view more details about that
property?

 

[Ken Snell MVP]

Common fields that I see between the two tables are Address, City, and
Seller. I assume that these would have the same values in both tables?

Also, it's not obvious to me what the relating fields are between the two
tables; perhaps ID and Contact #? You need a linking (relating) field in
each table so that the records can be tied together.

Overall, I'd think you could use a single table, if the answer to my first
question above is yes. It would probably simplify what you want to do as
well. And, I think I'd be inclined to use a subform instead of a second form
for displaying the pending information. The subform would show pending
information for listings that have pending status value. Then you don't need
to go back and forth between two forms, and data presentation will be easier
to do.

Post back your answers and reactions to what I'm saying, and we'll go from
here.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Arvin Meyer [MVP]]

I am new to access and am setting up a db for a trust account. I have a
table called listings that I want to input all the companies listed
properties and a form for those listings. I have a combo box labeled
"status"
and was wondering if when I change the status to pending in the status
combo
box if I can have it pull up a new form called pendings and pulling
information from the listing table to the pending form? I could then add
the
new information regarding the pending. I was using 2 separate tables but
someone suggested that I only need 1. I have 2 forms setup right now but
all
the information from the table is showing on both forms, I only want it to
show on the pendings form when the property goes pending. Please let me
know
what you think.
Thanks

The main difference between the 2 tables is that the data in one is Pending,
and the other is not. Yes a single table with an added Yes/No field will
suffice for both. You only need 1 form as well, since you can display data
(and even the controls) based upon whether or not the data is Pending. All
that need to happen is to change the recordsource to a query based upon
pending being true, and another query based upon pending being false. Here's
some quick aircode:

Sub Form_Current()
If Me.chkPending = True Then
Me.Recordsource = "qryPendingTrue"
Else
Me.Recordsource = "qryPendingFalse"
End If
End Sub

 

[Trevy via AccessMonster.com]

I am very new at this and need a little more detail. Where do I put the
aircode and what would the criteria be for my queries?
Thanks
Trevea

I am new to access and am setting up a db for a trust account. I have a
table called listings that I want to input all the companies listed

[quoted text clipped - 13 lines]

what you think.
Thanks

The main difference between the 2 tables is that the data in one is Pending,
and the other is not. Yes a single table with an added Yes/No field will
suffice for both. You only need 1 form as well, since you can display data
(and even the controls) based upon whether or not the data is Pending. All
that need to happen is to change the recordsource to a query based upon
pending being true, and another query based upon pending being false. Here's
some quick aircode:

Sub Form_Current()
If Me.chkPending = True Then
Me.Recordsource = "qryPendingTrue"
Else
Me.Recordsource = "qryPendingFalse"
End If
End Sub

 

[Arvin Meyer [MVP]]

In the form's Current event. Open the form in Design View. Open the property
sheet, Click on the Events tab and choose the Current event. Now click on
the ellipses button and paste the code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am very new at this and need a little more detail. Where do I put the
aircode and what would the criteria be for my queries?
Thanks
Trevea

I am new to access and am setting up a db for a trust account. I have a
table called listings that I want to input all the companies listed

[quoted text clipped - 13 lines]

what you think.
Thanks

The main difference between the 2 tables is that the data in one is
Pending,
and the other is not. Yes a single table with an added Yes/No field will
suffice for both. You only need 1 form as well, since you can display data
(and even the controls) based upon whether or not the data is Pending. All
that need to happen is to change the recordsource to a query based upon
pending being true, and another query based upon pending being false.
Here's
some quick aircode:

Sub Form_Current()
If Me.chkPending = True Then
Me.Recordsource = "qryPendingTrue"
Else
Me.Recordsource = "qryPendingFalse"
End If
End Sub