Open one form from another

[Dave1963]

G'day Access Gurus,

I am a complete newbie to Access and I'm trying to 1. learn something new, and 2. score major attaboy points with my boss with a wizbang design change tracking tool.

I have three tables that record different aspects of the data that I need to track relating to design change activities. The information is grouped by the design change lifecycle phase and aligned with the company policy for processing the design changes.
Next I created data entry forms bound the each of the tables. As the design progresses I need to update different information.
If I have updated data in one form, is it possible to open one of the other forms from the open form?
Ideally, the action on the open new form would also close the current form.

Thanks in advance

Dave

 

[MarkK]

It doesn't really matter in respect to your question, but what does this mean?

The information is grouped by the design change lifecycle phase and aligned with the company policy for processing the design changes.

Note that the biggest newbie mistake is incorrect table design, and this is your description of the table design.

But your question:

is it possible to open one of the other forms from the open form?

Yes. Commonly done using a button . . .

Private Sub cmdOpenOtherForm_Click()
[COLOR="Green"]   'open the other form[/COLOR]
   DoCmd.Openform "OtherFormName"
[COLOR="Green"]   'close this one[/COLOR]
   DoCmd.Close acForm, Me.Name
End Sub

. . . and get the form that does the opening to close itself.
hth

 

[Dave1963]

To answer your question I'll give a little bit of background.
Currently the tracking system is a spreadsheet with 90 columns, 4 active, 4 hidden worksheets, a huge amount of VLookups and countifs etc, plus a MSProject schedule that is approaching 5000 lines.

I looked at the data sources, who was reporting what metrics and how often data was updated. I realised that in Access about 10% of the Excel entries could be reduced to check boxes and the various calculations can be removed to queries that only run when a report is required.
Some of the data is entered once and not changed or, if a revision is released, one or two fields change. E.G. Change request version and approval date.
Other data is updated as the change moves through the lifecycle.
So rather than a mega table with 60 or more fields I split the data into 3 tables link by change number and gave each table a data entry form.
Maybe I am thinking too linearly but I thought queries would run smoothly if they referred to a table with 12 or so fields and not 60. I think there is also more scope for me to make mistakes if it was in one mega table.

From your question I anticipate you telling me I should do this in one mega table.

I thought that I could use a button to open the new form from the curent form but wanted to check first.

 

[MarkK]

No, I haven't made any comment on the suitability of your design.

All I've said is that I don't have enough info to assess your table design, and that it's a common pitfall.

Use one table to keep track of one type of thing. Use a related table to model a one-to-many relationship between two different types of things. So you might have a table for your CDs, and a related table for tracks on each CD, because this is how the reality is structured, that one CD has many tracks.

Similarly, your table design should model one-to-many relationships in your data without consideration for field-count or query speed. I you want more info on this topic search on 'database normalization.'

Cheers,

 

[Dave1963]

I had drawn everything up on flip charts showing who does what etc and thought that I had reduced everything down to the minimum in others words, by applying engineering rigour to the plan I thought I had normalized the data and how it interacts.
Your questions and points though, show me I have missed product baselines.
I have several one to one relationships between the tables and a couple of one to many links at the PBL level.
If field count is not a big issue in "good" db design I can merge all of the one to one fields into one table, the baseline one to many into another table and the serial numbers and Production batches into another.

Once again gurus I thank you for your time and advice.