How to customize template? (1 Viewer)

[BruceMalone]

Hello. I am new to Access and trying to build a database. I built one in excel but found that access is better suited. I am new with Access but was able to create a new simple database with table and form. I looked at the templates provided (contacts and northwind) and see it contains much more features that could be useful to me. I however do not know how to customize? I used the templates and deleted the fields from the table and forms but when using it gives me all kinds of issues. I also tried creating a new database and used the external database source to only import the objects I need but it doesnt work as intended either. Is there a simple guide or solution for me to customize these great templates to use for my own?

For example, in the contacts template I delete some fields from the contacts table that I dont need. But then when i go to the contact list form and try to do a search, it gives a message "Enter parameter value" E-mail address.

Thanks in advance.

 

[June7]

No, there is no simple guide or solution. Modifying a db involves much more than just deleting fields from tables. Queries may have explicit references to fields, fields might be used in expressions in textboxes, code (macros or VBA) could reference fields. Building a db from scratch has its challenges but can sometimes be easier than trying to figure out some other designer's logic and tracking down all elements that must be altered.

Some of those templates often use features many of us advise against: multi-value field, attachment field, hyperlink field, lookup fields in tables, spaces and/or punctuation/special characters in naming convention, reserved words as names of objects.

 

[BruceMalone]

No, there is no simple guide or solution. Modifying a db involves much more than just deleting fields from tables. Queries may have explicit references to fields, fields might be used in expressions in textboxes, code (macros or VBA) could reference fields. Building a db from scratch has its challenges but can sometimes be easier than trying to figure out some other designer's logic and tracking down all elements that must be altered.

Some of those templates often use features many of use advise against: multi-value field, attachment field, hyperlink field, lookup fields in tables, spaces and/or punctuation/special characters in naming convention, reserved words as names of objects.

Thanks. I'll try to build from scratch. One question, what is it called when you open a form and it shows the data table, but it has some buttons on the top where you can click to open the form, search ,etc. Like in the contacts template clicking new entry opens up the form in its own window. This might be easier than to have to open each form manually. If you can give me what its called i can do some research to build it in. Thanks again for the help!

 

[June7]

You could call it a "MainMenu" or "Search" form if you like.

Contacts template is set for Tabbed Documents mode. I prefer Overlapping Windows.

 

[BruceMalone]

So i created a main menu and added a subform to display the table. When I add a new entry to the main form, I want the sub area to refresh as well but I am not able to let it happen. I tried to put a macro to refresh upon load event but getting an error "the command or action refresh isn't available now". I tried adding the code me.refresh as well but still gives the same error :banghead:.

 

[June7]

What you originally described was search controls at top and records displayed below - that does not require form/subform arrangement.

Are main form and subform bound to same data?

Perhaps the form type name you were looking for was Split form. There is a form design wizard to build this style form.

 

[BruceMalone]

Attached is what I came up with. Not sure If i did what I originally wanted but it works for what i was looking to do.

 

[June7]

There is no need to "Refresh" when form loads.

You are using macros and VBA. I recommend using just VBA.

Again form/subform is not necessary.

Main form is unbound so what do you mean by "add record to main form"?

Perhaps you should explore the Split form wizard.

 

[BruceMalone]

There is no need to "Refresh" when form loads.

You are using macros and VBA. I recommend using just VBA.

Again form/subform is not necessary.

Main form is unbound so what do you mean by "add record to main form"?

Perhaps you should explore the Split form wizard.

Thanks. I'll check out split forms.

 

[Galaxiom]

Thanks. I'll check out split forms.

Split Forms are great for beginners but do not support code very well. Best avoided if you have high aims. You can emulate a split form with subforms that support a lot more functionality in code.

 

[Gasman]

Crossposted

https://www.mrexcel.com/board/threads/how-to-customize-templates.1118106/

 

[BruceMalone]

Split Forms are great for beginners but do not support code very well. Best avoided if you have high aims. You can emulate a split form with subforms that support a lot more functionality in code.

Thanks. Im playing with both methods to see which works best. In the subform method, how would I get the table to update automatically?


I have a main form which has the buttons to open the input form and show the table data. After inputing the data in the input form, the data doesnt show right away in the table data in the subform unless I click refresh all. Been trying to figure it out but havnt had luck.

 

[mike60smart]

Hi Bruce

You only have 1 Table for storing data

Your PaymentStatus and AppliedCountry have been hard Coded. I would recommend that these lists should be in their own tables. This would help when you need to add a New Country or Payment Status.

You should not use Lookup Fields at Table Level. Google "The Evils of Lookup Fields at Table Level"

It is recommended that you use an Autonumber field for every table.

You should Not have spaces in fieldnames.

 

[mike60smart]

In your database - Is an Agent Responsible for a Number of Workers?

 

[BruceMalone]

In your database - Is an Agent Responsible for a Number of Workers?

Hi. Yes it is.

 

[BruceMalone]

I was able to make it refresh by adding a event macro to refresh the form after clicking the button. Now another problem, I did not realize autonumber was unique and deleting a record wouldn't reset the numbers. Iv been reading to not use autonumber at all for numbering records but havnt found any solutions that say what to use instead :banghead:.

 

[mike60smart]

You should have a Main Form where you can select an Agent.

A Subform to record all the workers associated with the selected Agent.

See the example attached.

 

[BruceMalone]

You should have a Main Form where you can select an Agent.

A Subform to record all the workers associated with the selected Agent.

See the example attached.

Thanks. But i dont think that solves the issue where i am trying to add a reference number to each worker? Using autonumber if a record is deleted, it doesnt auto fix the record numbering.

 

[June7]

Autonumber gaps should be irrelevant. Why does it matter if there are gaps?

Autonumber is not supposed to have meaning to users.

If you want to assign a unique identifier to each worker, such as an employee ID number, and you want to control what that number is, that requires code.

Assigning custom unique identifier is a common topic of many threads.

 

[BruceMalone]

I’m just trying to find a way to show the number of records available. I guess I can do without it.