Form Capabilities (1 Viewer)

jyadayada

Registered User.
Local time
Today, 06:09
Joined
Sep 6, 2018
Messages
44
Hey

Thanks for taking the time to read, my first question is can Access Forms do what I need to do, namely:

I need to capture Location which is broken down into segments, Site / Block / Floor / Room, If I have dropdowns in a form, can I have it so that if Site A is selected, only Site As Blocks are available in the Block dropdown, if Block 1 (site A) is selected then only Site A, Block 1s Floors are visible in the Floor drop down and so on

and also, i would like 2 buttons, Save and New - saving the current submission and clearing the whole form for a new entry, and Save and New (Same Location) - saving the current submission and clearing all fields except the location fields in the instance of repeated submissions against the same location.

Thanks again for reading!

Jason
 

bob fitz

AWF VIP
Local time
Today, 06:09
Joined
May 23, 2011
Messages
4,717
IMHO an Access database can fulfill all of those requirements.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:09
Joined
Aug 30, 2003
Messages
36,118
I agree, and the first bit is known as cascading combos:

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:09
Joined
Feb 28, 2001
Messages
27,001
Absolutely yes, you can do as you describe.

I could try to do the combo-box description, but you would do better doing a search within this forum for the topic "Cascading combo boxes." That behavior exactly matches what you asked.

The combo-button trick is again possible. There the issue is perhaps made easier by creating two combo buttons using the Combo Button wizard. Do a 'save' button and a 'new' button - perhaps on a separate test form - so that you can see what the wizard builds for you. Then you can adapt that code for your purposes. As to "saving information" for the "New (Same Location)" case, you just copy the information you want to retain to local variables in the VBA code, then implement the "New" code, then before you exit the subroutine, you restore the copied information onto the new record. That means you would copy the wizard's "New Record" code twice and just customize one of the copies.
 

jyadayada

Registered User.
Local time
Today, 06:09
Joined
Sep 6, 2018
Messages
44
Thanks for replying!

Sorry I should maybe explain the use case a little, We're going to do an asset capture exercise, I want to have a form (or other method of submission) for the field engineers to submit asset details into a centralised location for ETL to a new system.

I presumes a Microsoft Form would be a good way of doing this to give the user a easy to navigate front end where I get the data in the back end in the Access Table(s)

Am I barking up the wrong tree or overcomplicating the thing?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:09
Joined
Oct 29, 2018
Messages
21,358
Hi. I hope you don't mind me saying this; but with database designs, everything starts out with your table structure. You would want to make sure you have a good table structure before you start thinking about forms. You wouldn't want to have a form idea drive the way you would design your table. Otherwise, you could run into some issues if you end up creating a bad table structure.

So, the first thing you need to be familiar with is Database Normalization. If you have no idea what it is, try to read some articles about it first, just to give you some idea. If you need help normalizing your tables, you can post your structure here, and we can give you a hand. Once you have a good table setup, we can help you create forms for it.

I do agree with you, forms will make it easier for your users to use your database. Actually, you shouldn't let your users see the tables at all. They should always go through a form to work with the data.

Cheers!
 

jyadayada

Registered User.
Local time
Today, 06:09
Joined
Sep 6, 2018
Messages
44
Thanks all again for the inputs

I do have a base grasping of normalising data / relational table design, I am now going down a rabbit hole of watching tutorials about cascading combo boxes, the example i'm watching currently is for a State / City example and I'm trying to find something that will give me the nuances of the 4 tiers I need

I shall probably return re help on that if needed of for the next step of the save and all new vs. save and all new except location issue

thanks again, speak soon!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:09
Joined
Feb 28, 2001
Messages
27,001
Treat the combo-boxes with a "divide and conquer" approach. Do them one at a time, perhaps using a test form, before going to the next one. The trick will be of course that the .RowSource queries can get more complex the deeper you get. But it is possible.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:09
Joined
Feb 19, 2002
Messages
42,981
The queries don't actually get more complex. Cbo4 has a where clause dependent on cbo3. cbo3 has a where clause dependent on cbo2, cbow has a where clause dependent on cbo1.

Many people replace the SQL as part of the cascade. I don't do that because I reference the form fields in the RowSource query:

Select ... From ... Where FldB = Forms!yourform!cbo1
Select ... From ... Where FldC = Forms!yourform!cbo2
Select ... From ... Where FldD = Forms!yourform!cbo3

In the BeforeUpdate Event of each combo, you clear the values of all lower combos to ensure that no invalid data remains

In the BeforeUpdate event of cbo1
Me.cbo2 = Null
Me.cbo3 = Null
Me.cbo4 = Null
Me.cbo2.Requery

In the BeforeUpdate event of cbo2
Me.cbo3 = Null
Me.cbo4 = Null
Me.cbo3.Requery

In the BeforeUpdate event of cbo3
Me.cbo4 = Null
Me.cbo4.Requery

Now that you see the pattern, you can have a hundred cascades if it floats your boat.
 

jyadayada

Registered User.
Local time
Today, 06:09
Joined
Sep 6, 2018
Messages
44
Here's an example with 5 cascading combos...Cascading Combo Boxes - Mendip Data Systems
Thanks for reference to the example, I can't seem to et at the design view though to see how it was built

Even though I stated a base understanding of normalising data, in my mind this would have sperate tables for each of the repeatable fields Site / Block / Floor / Room with a reference / key and the data in each, but if i need the cascading combo boxes to work do I need all 4 fields in each of these tables to allow for the selection of only the relevant data in the combo boxes?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:09
Joined
Feb 28, 2001
Messages
27,001
My previous experiences with cascading combo boxes included cases where some of the boxes were co-equal and optional, so in MY case the later options got more complex. In other cases, indeed the cascades stay at approximately the same level of complexity. Sorry if my response was misleading.
 

isladogs

MVP / VIP
Local time
Today, 06:09
Joined
Jan 14, 2017
Messages
18,186
Thanks for reference to the example, I can't seem to et at the design view though to see how it was built

Even though I stated a base understanding of normalising data, in my mind this would have sperate tables for each of the repeatable fields Site / Block / Floor / Room with a reference / key and the data in each, but if i need the cascading combo boxes to work do I need all 4 fields in each of these tables to allow for the selection of only the relevant data in the combo boxes?
Right click on the form & select design view from the context menu.
Or close the form then open in design view from the navigation pane
Once you have looked at how it works, you should have your answer
 

Users who are viewing this thread

Top Bottom