Re: Clearing Multiple Forms using Add New Record button.

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Hi All,

I'm using Microsoft Access 365.

I have created a database where customer details can be kept. Within this Database, there are 8 forms with one Homepage form. When opening Access, the user will be presented with the Homepage form where they can navigate around the database.

Screenshot only shows a snippet as there is sensitive data.

The CRM form is always the master and what the user will click on first, the same navigation buttons on the homepage are seen on this form at the bottom.

Screenshot only shows a snippet as there is sensitive data.

On that form, I have created an Add New Record button, where if the user clicks on this, it will blank out the data on the CRM form, however if they click on another button, like Marketing for example, the CRM form will close, the marketing form will open and the previously entered data will show.

What I want to happen is that, when the Add New Record button is clicked, all of the forms will be blanked out as if there is no data and waiting for the user to entry the data.

The forms have text boxes, yes/no check boxes and drop downs.

Can anyone help with this? I'm struggling.

Thank you.

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

Are the other forms linked to the main form by a primary key for the customer record?

If so you could change them to subforms on a tabbed page and they would then reflect the changes to the main form, e.g. goto a new record.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Are the other forms linked to the main form by a primary key for the customer record?

If so you could change them to subforms on a tabbed page and they would then reflect the changes to the main form, e.g. goto a new record.

Thanks for your reply. There is one table called "Publisher Form", this table holds ALL of the information that the user will put in, in a table format.

The user will navigate this table by using the Forms. The forms are broken down by department/area for ease of use for users.

Options > Current Database > Document Window Options is set to Overlapping Windows.

When a user clicks on CRM from the Homepage Form, it will close the Homepage Form and open the CRM Form. When the user clicks on another area from the CRM Form, it will close that CRM Form and open the new one. Does this make sense?

Eventually there will be validation put in, so some fields are filled out as mandatory before moving to the next section.

Does that answer your questions?

Sorry, I'm a newbie.

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

I understand your description.

Personally I would make this form tabbed, with each department on a tab with an appropriate label. The form itself can be a normal overlapping one.
This is far easier to navigate around and solves your related data problem.

The tabs can be separate forms with their own validation, but using the subform idea they would automatically be linked to the main one, even if the data source is the same underlying table.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

I understand your description.

Personally I would make this form tabbed, with each department on a tab with an appropriate label. The form itself can be a normal overlapping one.
This is far easier to navigate around and solves your related data problem.

The tabs can be separate forms with their own validation, but using the subform idea they would automatically be linked to the main one, even if the data source is the same underlying table.

Hi,

Thanks for your reply.

How would one go about changing it from the current split 8 or so forms, to tabbed without having to re-do all of the work?

Is there definitely no way, that some code can be used or something so that when the add new record button is pressed, it then clears all of the forms?

Thanks.

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

You can probably simply drop the existing forms on the empty tab pages and make some minor adjustments and they will work with a little bit of fiddling. (Depending on what code you have in the forms...)

You can certainly do what you want, but you would have to check for each form that was loaded and either close it (I'm guessing re-opening would put you onto the correct new record) or set it to a new record, this would then not be in sync with the new record unless you also added code to recheck which record you where on, on each form when it was activated. As you can see this is much messier and probably error-prone.

 

[Pat Hartman]

Re: Clearing Multiple Forms using Add New Record button.

It makes most sense to use the same form for view/edit/add. That way all the validation code for a set of data is in only a single place. From a menu, you can have multiple options for each form if that is what you want to allow you to open a form for adding or to open a form already filled with data.

I do something else. I optimize all my apps for client/server operation so that I can upsize them to SQL Server within an hour if the app gets to that point. Luckily, the best practice for client/server also works just fine for Jet/ACE. My forms are all bound to queries that limit the rows returned. I also provide search forms for complex searches since I don't ever use local filters. For a normal customer form for example, I would have an unbound text field or combo in the form's header. The RecordSource query would have criteria that points to that field -

Where CustID = Forms!myform!cboSearchCustID

-So, when the form opens, the search combo is empty and the form opens to a blank record ready for adding a new customer. If the user wants to look at an existing customer, he picks the customer, usually by name, from the search combo.

 

[isladogs]

Re: Clearing Multiple Forms using Add New Record button.

@TPS
I've moved your post with screenshot to this thread and deleted the other thread.
Please don't create duplicate threads on the same topic

 

[Pat Hartman]

Re: Clearing Multiple Forms using Add New Record button.

Now that we have the visual -

I would not use a button for the new record. A button action happens ONCE, it is not a setting. If you want "new record" to be a setting, use an option group with new, edit. Delete is NEVER a generic option so that doesn't belong in this list at all.

Using the option group, gives the subforms something to check so in their open events, they can change the form settings based on the value of the option group.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

@TPS
I've moved your post with screenshot to this thread and deleted the other thread.
Please don't create duplicate threads on the same topic

I didn't see the first one get created. It said an administrator had to approve it, then I read that users with less than 10 posts cant post images and it will get rejected. Hence making a new thread.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

You can probably simply drop the existing forms on the empty tab pages and make some minor adjustments and they will work with a little bit of fiddling. (Depending on what code you have in the forms...)

You can certainly do what you want, but you would have to check for each form that was loaded and either close it (I'm guessing re-opening would put you onto the correct new record) or set it to a new record, this would then not be in sync with the new record unless you also added code to recheck which record you where on, on each form when it was activated. As you can see this is much messier and probably error-prone.

So best way forward is recreate what I've done but change Options > Current Database > Document Window Options to Tabbed Windows?

And then create a tabbed form? If add new is then pressed on the main CRM form, the tabbed form will automatically clear the other forms? Will the search also return back the relevant data on the other forms?

The code is only on the buttons and it closes the existing form and opens the new one.

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

No - you can create the tabs within a floating form, which is what probably 90% of the developers on here use.

Can you see the controls Page33 and Page34 below;

This is a tab control with a subform on it, as you can see the main info is always shown in the top e.g your client name/account number, then each tab would have the department-specific info on it.

Does the picture help?

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

No - you can create the tabs within a floating form, which is what probably 90% of the developers on here use.

Can you see the controls Page33 and Page34 below;

This is a tab control with a subform on it, as you can see the main info is always shown in the top e.g your client name/account number, then each tab would have the department-specific info on it.

Does the picture help?

Not really, how do I go about creating the main form and the subform?

I am using Access 2016.

 

[arnelgp]

Re: Clearing Multiple Forms using Add New Record button.

add a Global variable to your project.
on a New Module:

Global gblOpenNewRecord As Boolean

on the Add button click event, set this variable to True

private sub addButton_Click()
gblOpenNewRecord = True
end sub

on Delete, Find, Go Home buttons, (Click Event) set this variable to False.
on each Form (publisher, AP, Marketing, etc), add code to its Load Event:

private sub form_load()
If gblOpenNewRecord Then
    ' goto new record
    Docmd.GoToRecord,,acNewRec
End If
end sub

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

add a Global variable to your project.
on a New Module:

Global gblOpenNewRecord As Boolean

on the Add button click event, set this variable to True

private sub addButton_Click()
gblOpenNewRecord = True
end sub

on Delete, Find, Go Home buttons, (Click Event) set this variable to False.
on each Form (publisher, AP, Marketing, etc), add code to its Load Event:

private sub form_load()
If gblOpenNewRecord Then
    ' goto new record
    Docmd.GoToRecord,,acNewRec
End If
end sub

Thank you, will give that a go.

General question, do you recommend having a table with 102 fields or would you break this up by department. Giving each table an ID-Autonumber so they all link together?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

You create the form and subforms just like any other? Slightly confused by that statement as you appear to have created the forms already, I'm suggesting using them in a slightly different way.

2nd point:

a table with 102 fields

I would suspect that your data isn't normalised, or at least does need breaking down.
Could you post a picture or list the field names? If you list them enclose them in CODE tags to make it readable (Use the # symbol in the editor.)

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

You create the form and subforms just like any other? Slightly confused by that statement as you appear to have created the forms already, I'm suggesting using them in a slightly different way.

2nd point: I would suspect that you data isn't normalised, or at least does need breaking down. Could you post a picture or list the field names?

I haven't created any subforms. What I have done, is made a homepage form, on that there are buttons to another form. There are no subforms within that form.

Logically, the user would do CRM details, then Accounts, Then Customer details. etc.

I can't list all of the data fields for data protection but I will try and explain. I have created table_information.

Table_Information holds the CRM details, accounts, customer details. I then have multiple forms laid over this table which breaks the table up by department. There is only one master data table.

Name
Code
Product Group
Contact Name
Email address
Phone Number
Fax Number
Account Details
Payment Details
Marketing Details
etc.

User will click on CRM and enter data for fields related to Name/Contact.
User will then click on Accounts, CRM form will close, Accounts will open and enter Account Details and Payment Details.
User will then click on Marketing Details. Accounts form will close. User can then enter Marketing Details on the Marketing Form.

Make sense?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

Subforms are simply forms that are displayed inside another form. No different at all. By embedding them you can use some additional functionality that makes your life easier.

It's only field names I'm after not the actual data, I doubt there is anything in a field name that would breach data protection.

 

[arnelgp]

Re: Clearing Multiple Forms using Add New Record button.

do you recommend having a table with 102 fields or would you break this up by department. Giving each table an ID-Autonumber so they all link together?

that's definitely a No.
it might not be apparent now, but in the future this will cause you headaches.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Subforms are simply forms that are displayed inside another form. No different at all. By embedding them you can use some additional functionality that makes your life easier.

It's only field names I'm after not the actual data, I doubt there is anything in a field name that would breach data protection.

I put the details on my last reply, maybe you missed it:

Name
Code
Product Group
Contact Name
Email address
Phone Number
Fax Number
Account Details
Payment Details
Marketing Details
etc.

Database will open up and display the homepage.
User will click on CRM and the homepage will close. The user can then enter data for fields related to Name/Contact.
User will then click on Accounts, CRM form will close, Accounts will open and enter Account Details and Payment Details.
User will then click on Marketing Details. Accounts form will close. User can then enter Marketing Details on the Marketing Form.

that's definitely a No.
it might not be apparent now, but in the future this will cause you headaches.

So makes sense to break it up by area/department? Correct? And have each table linked with ID-Autonumber?