Combo box dependant Form

[Sqwuishy]

Hello!

I am very new to Access and VBA, so please bare with me if I am being dumb

I am creating a database to track demands for items, place, update etc. So far i've been managing quite well just using YouTube and Google. However i've been stuck on this next part for 6 hours and am totally stumped!

I have created a multiple forms to place demands against each account. To make it easier for the users, I am trying to create a Home Form in which they can select which Account they want to demand against and to right there. So far on this Home Form I have a ComboBox linked to a master sheet, and a button.

Through trail and error I have managed to get the button to load a Form, however my current issue is that no matter what I have selected in the ComboBox, only the first form in the coding ever opens!

Private Sub NewDemand_Click()
On Error GoTo NewDemand_Click_Err

If cboTailNumber = ZZ382 Then
    DoCmd.OpenForm "frmZZ382Demand", acNormal, ""
     
Else
    If cboTailNumber = ZZ383 Then
    DoCmd.OpenForm "frmZZ383Demand", acNormal, ""
       
   
NewDemand_Click_Exit:
    Exit Sub

NewDemand_Click_Err:
    MsgBox Error$
    Resume NewDemand_Click_Exit
   
    End If
End If
End Sub

There will be approximately 50 forms, which I am guessing I will just have to copy/paste and change names. For now just trying to get it to work with two XD

I almost hope I am just being silly and missing something easy!

 

[plog]

I fear you have bigger issues than this one you posted about, but let's start with your code. There's a lot of wrong

If cboTailNumber = ZZ382 Then

First, cboTailNumber is a variable. You don't literally mean the characters 'cboTailNumber', you mean whatever is in cboTailNumber. That is correctly done. However, ZZ382 is also a variable and I don't think you mean it to be. I bet you literally mean the characters 'ZZ382'. See what I did there? You do the same thing in code, when you literally want the characters themselves and not to use a variable you put quote marks around the literal string (that's actually the term for it--a literal)

Else
      If cboTailNumber = ZZ383 Then

Same issue here, but more. You don't want just an Else and on the next line the if, you want to use an ElseIf. Especially if you are going to keep testing cboTailNumber against values. There's a more efficient way to do this than with ElseIfs, but that's a course for a different post and probably not even applicable after I drop the bombshell at the end.

If cboTailNumber = ZZ382 Then
    DoCmd.OpenForm "frmZZ382Demand", acNormal, ""
     
Else
    If cboTailNumber = ZZ383 Then
    DoCmd.OpenForm "frmZZ383Demand", acNormal, ""
       
   
NewDemand_Click_Exit:
    Exit Sub

NewDemand_Click_Err:
    MsgBox Error$
    Resume NewDemand_Click_Exit
   
    End If
End If

I posted the whole this time because you have a whole code problem--your End Ifs aren't where they should be. The first EndIf listed closes the If immediately above it (if cboTailNumber = ZZ383), which means if it happened to work and hit the code for ZZ383 it would also hit your Click Exit and Click Err code because they are inside that if. You need to be careful where you put your End Ifs, it can cause code that shouldn't run to run.

So that's the code errors and...they are all probably irrelevant. Why 50 forms? Whats the difference among them? Are they all completely different looking? Or is there just a slight difference--maybe 1 or 2 inputs but everything else is the same? Or worse yet, are they exactly the same but work for different "types" of your data? To use a car analogy--do you have a Ford form and a Chevy form and a Toyota form and a Nissan form and a..etc etc?

 

[XPS35]

I agree with plog that having so many forms looks like a design problem. What table do you have?

An other problem with your code might be the event that triggers it. I think the trigger should be the after update event, not the on click.

 

[arnelgp]

you can use 1 form (subform in my case) for all.
see this demo. my table may not match your table structure.

 

[Sqwuishy]

Hey guys,

Thanks for all the replies! I've tried adding "quotation marks" around ZZ382 etc, however then no Forms open at all.
Looking at your demo arnelgp, i'm wondering if I am spreading too much out? And with Plog, you maybe right I might not need this many forms?

What I have at the moment is a "Master Table" for dropdowns, i.e demand types, ZZ's etc.
Then I made a table for every ZZ, however only the table name references this. I presume I should be using just one table for ALL of the ZZ's, then adding a column to dictate which ZZ each line is for instead of a table for each.

 

[XPS35]

I presume I should be using just one table for ALL of the ZZ's, then adding a column to dictate which ZZ each line is for instead of a table for each.

That is the right approach!

 

[Gasman]

Even if you keep your ill advised structure, with your naming convention, you can open the correct form with just

DoCmd.OpenForm "frm" & Me.cboTailNumber & "Demand", acNormal

assuming ZZ382 etc are values and not variables.

 

[Sqwuishy]

I've changed it all to one table, 1 form to submit new ones.

Now to teach myself how to link combo boxes dependent on values, run queries, edits, and highlight stuff if a value xD Google and YouTuber here I come!

 

[Pat Hartman]

Welcome to our world And to AWF

You've made a huge step but understanding that one form is the solution. Please post the corrected form so we can be sure you understood the instruction before you move on. When you find yourself thinking you need multiples of something, think about "what would Amazon do". Would they have a form for every customer? I don't think so Eventually, you will come to understand that you will have a lot of rows in tables rather than a lot of forms or tables.

You haven't asked any of these questions yet but I'm going to post links to several of the sample databases I created. The samples have interesting features that you might want to include as well as useful coding techniques in addition to showing how to solve particular problems. They are also good models for relationships and naming standards. I also included a video about data validation which you will need to understand sooner rather than later as you travel this journey. It also talks about form and control events and when they fire. This is critical to helping you understand how forms and reports actually work.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

Replacement Switchboard

This Switchboard form is based on the last VBA version which I think was A2k. I expanded it to allow for 12 items rather than 8 and you can easily change it to do more. I've included directions. This updated version has two switchboards. One more traditional and the other is big buttons...

www.access-programmers.co.uk

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

Chaining Forms

Many people open multiple forms at one time. This is especially prevalent when you are using the tab view which is the default. I find that to be confusing for users so I developed a simple technique that uses the OpenArgs to allow one form to open another in DIALOG mode and then hide itself...

www.access-programmers.co.uk

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com

 

[Sqwuishy]

Thanks for the list of things to read Pat!

I've attached what I made so far so you guys can see where i'm heading towards hopefully, still yet to tidy up the demands. Next would be to link AinU and TailNo boxes. Then finally a form/report? for people to search the table via 'Demand No' or 'NSN' etc.
The last ticket I would probably do would be for a button to be on home page which changes colour if the 'Demand Status' field is 'New', so the stores people know to place the demand. However this is all extra stuff to learn about!

I think my biggest issue is I jumped into Access thinking I could just play around and create this, like you would with Excel. However there is a lot more to this than Excel, especially if you want it to be user friendly

After spending the last few days just spending hours amending the forms via trail and error (in a way to self teach and see what changes what), I probably need to put aside a few days to sit down, watch some videos, read Pat's linked tutorials etc. Get a stronger idea of the basics and what affects what, for example how relationships and queries fit into creating forms, before I move back onto this database I am making!

Thanks again for everyone's input!

I found this guys video tutorial "Computer Learning Zone" on YouTube (for some reason putting link marks as 'spam' and won't let me post). There are a lot of tutorial videos which are all quite long. He has 26 videos for 'beginners' then some more. From the little i've glanced over it seems good? Debating if it's worth continuing to edit my form, or starting fresh and creating a new database as I watch the videos.

Hopefully my current database is a good starting point! That or you guys will rip me apart haha

 

[arnelgp]

are all the records in your tblManagementData Related?
it is highly Not Normalized.
you should consider putting Each Column into Its Own Table.
then build a Combobox for that table.

 

[Sqwuishy]

are all the records in your tblManagementData Related?
it is highly Not Normalized.
you should consider putting Each Column into Its Own Table.
then build a Combobox for that table.

In the management table, I would only be linking 'Tail Number' to 'AinU Code' as they are directly relatable. Everything else was just for combobox drop downs in the form. Putting it all into one table is definitely a bad habit I've just brought over from Excel Did not realise that they should be in their own table in Access, presumed I could link certain things just via the forms.

I most definitely need to spend a few days watching that chaps videos! Perhaps watch a tutorial once, then once again whilst physically copying him as a way to drill it in and learn that lesson. Then once I've gone through his probably 20 hours worth of videos xD put that into practice for creating my own database from scratch with the data and layout I am after.

 

[Pat Hartman]

You need to read some more about relationships. I can't tell which of your tables is the parent and which is the child because you have duplicated the data in both tables. That isn't how relational data works. Take a look at the many-many or the table maintenance examples to see relationships. The only repeated data is the foreign key which is the primary key of the parent table stored in the child table where we call it the foreign key. These are examples of working databases, not reading or videos so look as long as you need to. open the objects and see the code. open the relationship window and see the relationships. open the queries and see how tables are joined and some data comes from each table.

Do not continue before you can properly define each table.
Also, restrict the characters in your names to (a-z), (A-Z), (0-9) and the underscore. Avoid spaces and never use special characters. You also need to avoid reserved words such as the names of properties and functions like Name and Date. I use two-part names when I think I might be using a reserved word such as TaskDesc or CustName. They are simple and there are hundreds of them between Access, VBA, and SQL. Most don't cause trouble but "Name" and "Date" and "Month" and "Year" do and those are the most common mistakes. What is Me.Name? Is it your Name field or is it the name of the object?
And finally, use "short", descriptive names but single letters don't help anyone.

 

[GPGeorge]

Sometimes it an help to step back and take an overall look at the components of a relational database application.

They consist of three distinct components, each of which fills a specific role.

1. Data layer. Tables store your data. It's more than storage, though, in a relational database application (as opposed to a flat-file like a spreadsheet). Each table contains the data for one entity. tables are related to one another on the basis of how they work together. To take a very basic, common, example. Organizations hire people. In other words, one common relationship between organizations and people is employment. People are hired by organizations; organizations hire people. There are, of course, other ways in which relationships can be formed, but this is one common to many, many, relational database applications.
   1. The key thing you need to grasp here is that details about each of these entities is stored in one, and only in one, table. Organization names, for example, are stored only the Organization table. People names are stored only in the People table. No people names can be stored in the Organization table. The relationship, hire, has to be indicated in some way, of course, but NOT by putting their names in each other's tables. The exact way that is done depends on both the purpose of the relational database application and the business rules set up to manage it.
   2. An important, but frequently misunderstood, consequence of this fact is that data and relationships are managed and enforced in tables, not in forms, which are the second element of a relational database application.
2. Interface layer. There are two primary components of an interface.
   1. Forms are tools through which users interact with the data. Think of this more like a baking dish than cake batter. The cake consists only of the baked batter, but the baking dish allows the baker to move the batter into and out of the oven in an organized, consistent, fashion.
   2. Reports are the tools with which users present the data for consumption by other users. Think of this more like a serving dish. Once the data has been combined, baked and laid out for the end user, it's put into a report for serving.
3. Logic layer. This is the code with which users manage both data and some parts of the interface. In Access we have two coding languages, one really simple, one really powerful. Macros do the light lifting tasks, but for real power, flexibility and reliability, VBA is the proper tool. You can think of VBA like the recipe and the measuring and mixing bowls, spoons, beaters and other tools used to create, decorate and serve the final product. The more complex the cake, the more sophisticated the recipe and tools need to be.

So, when you say, for example, that you wanted to have a single table and handle the rest in forms, you were not yet aware of the whole structure of a relational database application you'd actually need. Actually, those are the most frequent problems new Access users run into. They don't invest enough time and effort into understanding how relational databases work, i.e. table design, and they think they can compensate for inadequate or inappropriate table designs with fancy interfaces and clever VBA. It's possible, to some extent, to workaround those limitations, but that is also the most fragile and difficult way to do it.

Get the tables set up correctly first. Then look at the forms and reports you need to both present the data to the end user and to manage it during the data entry phase. Add the logic as the forms and reports grow.

 

[Sqwuishy]

Hi guys!

So I've spent the last week watching tutorial videos every evening after work and learnt a lot. I got to the point of having a form which worked as I wanted it too, however when I tried to set up forms which would run off a query to add additional information, or run reports, I kept running into issues. I presume it may be something to do with how I had my relationships/tables set up? For now I have removed my forms/relationships to try and get the baseline right.

The idea of this database is that the information in "DemandT" will be different for every line, the information in "ItemT" will also be different for every line, however it can be used multiple times on each demand. The remaining tables are all data which never changes.
Each "Aircraft" can have multiple "Demands" against it. Each "Item" can be used multiple times on "Demands". The "DmdStatus" is a set value linked to a "Demand", however it can change. "Tailno" and "AinU" are directly relatable.
The "ID" fields in each table are primary keys as autonumbers. I believe this is the right thing to have, with the information not required for myself but for access?

In the long run I want to be able to create my Form again, in which I can place a Demand against an Aircraft. Then a different person can run a Query to see any Demands with Status of "New", add additional information and change the Status to "Live". Subsequently a report which can be ran to show any Demands with Status of "Live".

I've got the plan all in place (I think) and thought I had a good idea of what I was doing, with my forms working. Managed to get a Query to work, however it was not showing all the information I required. When I tried adding additional information to be displayed I was getting "mismatch" errors, which is why I am thinking that my relationships/tables are incorrect, and why my Queries/reports are not populating as I want. I have tried to put "ID" lines in other tables to link the primary keys, however as I have found out I can only have a single autonumber field, as such this column will be blank?

PSB my current relationship layout, can anyone see where I am going wrong? Should I have an additional table as a 3rd man link? I've watched a few videos but not gained much more information on the design I am going for. I did try to download a few "templates" however they were only showing for example a business and its employees, where as I am trying to link a few. I may be getting too far out of my depth and trying to do too much?

 

[Sqwuishy]

Update: So I figured out that I was getting mismatch because I had relationships between "autonumbers" and "short text". I've created some more "ID" fields as "number" for relationships. Using form wizard (just a quick check to see if tables load in form together) i've managed to get rid of that mismatch. The table loads fine in design view, but opens blank with nil errors in normal view?

 

[XPS35]

I think some of your relations are wrong. You also need to enable the relation property "enforce referential integrity". That will keep your data clean. It also makes clear which table is on the "1 side" of a 1 to many relation and which on the "many side".

I did this for all (but 1) relations. It shows that some relations (red box) are "running the wrong way". One status can be related to many demands, a demand has one status. So StatusT should be on the "1 side" and DemandT should be at the many side. Hence DemandT should have StatusID as a field and DmdID must be removed from StatusT.
If you want to store multiple status for one demand (many to many) because you want to keep history, you need an extra table.
This all applies to all tables/relations in the red box.
Look at ItemT en UloT / MACcT; there you do it right.

I could not enable the relation property of the AircraftT - AinUT relation. Try it and you see the problem.

 

[Pat Hartman]

I don't believe that @XPS35 fixed the relations correctly. Of course it is impossible for us to guess but, Only AircraftT, ItemT, UloT, and MACcT seem to have valid relationships. If the other fields are "lookups", then then the ID fields of those tables would point to FK fields (preferably with the same names) in DemandT

If I guessed right and these extra tables are supposed to be lookups, you need to decide if they are required and set the Required property in DemandT if they are but first, you'll need to add the values or you'll get errors because you are making the fields required without the record having a value.

 

[XPS35]

I don't believe that @XPS35 fixed the relations correctly.

I did not fix anything. I just set enforce referential integrity for every relation to yes. Read carefully.

 

[Pat Hartman]

You went along with the premise that the the PK of the parent table belongs in the lookup table though. Did you look at the actual data? If you do that, then the lookup value can only ever be assigned to ONE parent record. In this short example only ONE Record can be New. ONE record can be Live, etc. and once you get past the 5th option, the thousands of other records can't have any status at all unless you want to add New, hundreds or thousands of times.

StatusID    DmdID    DmdStatus
1        New
2        Live
3        Satisfied
4        Rejected
5        Cancelled