Click on a form and bring up another form with a coorisponding record

[bassstuf]

I am new to access and am trying to build a database and have a form thats
list a few fields, and when you click a reference # on one form (autonumbered
field on main table), it will display the form that has all the data listed
from the main table. I tried Macros, but couldnt get it to work.

Thanks

Dave

 

[Steve Schapel]

Dave,

If I understand you correctly, you would use an OpenForm action in your
macro, and then in the Where Condition argument you would put something
the equivalent of this:
[reference #]=[Forms]![NameOfYourFirstForm]![reference #]

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field or control.

 

[bassstuf]

Thanks, ill give it shot and I didnt mean to put the "#" sign in the message,
its not on my fields

Another question, I have a "status" field, which has the options of an open
or closed. I have to 2 columns (date opened and date closed)...is there a way
that when I select "open" in the status field, it puts in the date, and same
when i select close? I have been trying different things for a few hours now.

Thanks

Dave,

If I understand you correctly, you would use an OpenForm action in your
macro, and then in the Where Condition argument you would put something
the equivalent of this:
[reference #]=[Forms]![NameOfYourFirstForm]![reference #]

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field or control.

I am new to access and am trying to build a database and have a form thats
list a few fields, and when you click a reference # on one form (autonumbered

[quoted text clipped - 4 lines]

Dave

 

[Steve Schapel]

Dave,

Hmmm... You could put a macro on the After Update event of the Status
control on your form, with two SetValue actions, like this:

Condition: [Status]="open"
Action: SetValue
Item: [date opened]
Expression: Date()

Condition: [Status]="closed"
Action: SetValue
Item: [date closed]
Expression: Date()

However, I am bound to say that this would very liwould be regarded as
poor database design. You probably should not have a Status field at
all. Status of open/closed can be derived from existing data (i.e. the
dates), and as such should not really be stored independently in the
table, as it can always easily be returned via a query or expression,
whenever you need it for your purposes of form or report.

Therefore, to turn your question upside down, you can enter your date
opened and or date closed directly (hint: there is a built-in keyboard
shortcut for this... Ctrl+; or else you could set up a button to click
to enter the current date). And then instead of the Status being a
field in the table, put an unbound textbox on the form, with the Control
Source set like this:
=Switch([date closed] Is Not Null,"closed",[date opened] Is Not Null,"open")

Hope that makes sense. You said you're new to Access, so we might as
well get you off on the right foot.

 

[bassstuf via AccessMonster.com]

Ive pretty much set this up, now im just having problems with the VB code to
reference the "Date Closed" Field on the Form

Dave,

Hmmm... You could put a macro on the After Update event of the Status
control on your form, with two SetValue actions, like this:

Condition: [Status]="open"
Action: SetValue
Item: [date opened]
Expression: Date()

Condition: [Status]="closed"
Action: SetValue
Item: [date closed]
Expression: Date()

However, I am bound to say that this would very liwould be regarded as
poor database design. You probably should not have a Status field at
all. Status of open/closed can be derived from existing data (i.e. the
dates), and as such should not really be stored independently in the
table, as it can always easily be returned via a query or expression,
whenever you need it for your purposes of form or report.

Therefore, to turn your question upside down, you can enter your date
opened and or date closed directly (hint: there is a built-in keyboard
shortcut for this... Ctrl+; or else you could set up a button to click
to enter the current date). And then instead of the Status being a
field in the table, put an unbound textbox on the form, with the Control
Source set like this:
=Switch([date closed] Is Not Null,"closed",[date opened] Is Not Null,"open")

Hope that makes sense. You said you're new to Access, so we might as
well get you off on the right foot.

Another question, I have a "status" field, which has the options of an open
or closed. I have to 2 columns (date opened and date closed)...is there a way
that when I select "open" in the status field, it puts in the date, and same
when i select close? I have been trying different things for a few hours now.

 

[bassstuf via AccessMonster.com]

I actually figured this out, but because how my DB is setup (and need to keep
it that way), I need to figure out how to write an after event to send the
data to the table? Any Clues (Ive been reading alot of threads and I need to
have the result in the main table)

Ive pretty much set this up, now im just having problems with the VB code to
reference the "Date Closed" Field on the Form

Dave,

[quoted text clipped - 33 lines]

 

[Steve Schapel]

Dave,

I had assumed the form was bound to the table, and the Status, date
opened, and date closed controls on the form were bound to these fields
in the table. Is that not so? If so, what is the form based on? If,
in fact, it *is* based on the table, did you try my earlier solution,
using the SetValue macro actions? If so, what happened?

 

[bassstuf via AccessMonster.com]

All the fields are bound to the table, and i setup the second way you
described (with the "switch" and it works great, so i would really like the
result "either open or closed" to show up on the table or another table.

The set-value wouldnt give me any results (either in date open or closed
fields)

 

[Steve Schapel]

Dave,

The whole #1 point of my earlier post is that the result open/closed
*shoud not* show up in a table. It flouts database design principles.
There is no reason for it to be in a table. Tables should never be seen
anyway, in normal database operation - their purpose is background data
storage. So it shouldn’t make any difference to you whether it is in
the table or not, so long as you can see it on your forms and reports
whenever you need to... which you have said you can, via the Switch
expression I gave you.

However, if in spite of that you are still determined to have a Status
field in the table, then the data "open" or "closed" will have to be
either entered in there via a bound control on the form, or via some
macro or code. But you can't have it both ways, you see. You started
off by saying you would enter open or closed, and then the dates would
be automatically entered accordingly. Well, you can't then have the
open or closed entered based on the dates... this is the snake eating
its own tail.

My recommendation is:
No Status field in the table.
date opened and date closed fields in the table, with controls on the
form bound to them, and dates entered manually as applicable.
An unbound Status textbox on the form, to display the status open or
closed via the Switch function based on the dates.

If you want somethin different, I have got a bit confused now about
exactly what you do want to happen.