Noob needs help with form design

[Motoman]

First of all, allow me to apologize for asking what is surely a noob question. I live in SQL all day, but I don't actually ever use Access. I have a need to create a very simple little database/application and I know Access can do these things, I just need a kind soul to point me in the right direction. I've spent some time lurking around and watching YouTube tutorials, but I'm not really finding what I need. Maybe because I don't know how to ask.



In a nutshell, there's a particular type of racing that I help manage, and we've had a little application that someone built maaaaaany moons ago that we use to manage our events. It's hopelessly old, and is built on tech that doesn't even exist on Windows 7 anyway - let alone Win8. So various clubs are either running old XP machines still, or running this thing in a WinXP virtual machine on a Win7 box. It needs to go...

For those who would like to help, I made a little document that shows some screenshots with descriptions of what the application needs to do - http://www.kingofthehill.org/wp-cont...ccess_help.pdf

Basically, the system needs to handle competitor registration, and then record scores as the event goes on. My first problem is figuring out how to make a form that has a combo box that will let a user type in a competitor's name, and then populate the other fields from the existing record - or, in the case that it's a new customer, send you to the form to create a new record.

I've bounced around the internet a bit, and tried watching some tutorials on YouTube, but haven't really found what I'm looking for. I may not know how to ask properly... I have no doubt that this type of Access application has been created a bajillion times before, as the basic functionality is, well, basic. Pretty much the same thing as a simple customer/orders application, or something like that.

Anyway...if anyone could point me in the right direction I would be eternally grateful.

[June7]

If you want to use a combobox to select a competitor and if competitor not in the list then can use the NotInList event of the combobox to enter the new competitor 'on the fly'.

Actually, your first problem is getting the data structure correctly set up. Worry about the user GUI later.

Will need to develop understanding of relational database principles, Access functionality, programming concepts, macro and/or VBA coding, SQL (you may be one up on that).

[ItsMe]

What you are proposing is not simple and will require knowledge/understanding of RDBMS principles before step 1, let alone before form design.

I have built a motorsports DB for a small circuit and it was rather complex. I am not trying to discourage you but you need to start with your tables/relations.

[Motoman]

Actually, the RDBMS design is the one thing I don't need help with. That's basically my day job

I just haven't ever used Access for anything before.

So really, I just needs some GUI hints.

[ItsMe]

Create a query object and bind a form to that query as its Recordsource.

The combo can be assigned its own SQL via its RowSource property. There is not a need to include all fields in the form's recordset because additional controls like Comboboxes, ListBoxes, and Option Groups can update Foreign Keys in the form's recordset.

[Motoman]

I almost have something. I think. I set up a query and made a form from it, and it shows me the list of existing rider names in that first drop-down...

...however. When I either try to select a name other than the first in the list - or try to type in the combo field - it dings at me and flashes a message at the bottom that says "The name field is read-only."



And I can't see anything in the properties about it being read-only. I see a "Locked" option, but that's set to No. What blindingly-obvious switch am I missing here?

[June7]

See my previous post for GUI hint: NotInList event of combobox. http://support.microsoft.com/kb/197526

Post the SQL of the RecordSource for the form.

Post the SQL of the RowSource for the combobox.

[ItsMe]

I see you have Access 2013. Because of this, advice on where stuff is may be a little spotty.

Here is another tip. Don’t use the Wizard or Layout View to create forms. Use “Form Design” under the Create tab within the Ribbon.

[Motoman]

Here's the SQL for the form's record source:

SELECT Riders.Rider_Full_Name, Riders.Rider_City, Riders.Rider_State, [Class/Entry].Class_Name, [Class/Entry].Rider_Number
FROM Riders LEFT JOIN [Class/Entry] ON Riders.Rider_ID = [Class/Entry].Rider_ID;

Had to make outer joins because at the beginning of an even there will be no entries at all...

Here it is for that combo box:

SELECT Riders.Rider_Full_Name, Riders.Rider_ID
FROM Riders;

I've tried tying the combo box to the same query the form works from, and that doesn't seem to work at all (no names populate into the form to start with). Having it as it is now, the combo box populates with the names I put in the table for testing, and the city & state for the first row...but I can't change select any of those other riders.

I don't think I'm at the point where I have to worry about the NotInList bit yet - since I can't get the In List part to work

Although I have to say I'm a bit disappointed that there's so much coding needed to do this sort of stuff. Apparently I'm far too gullible - people just told me that "Oh yeah, you just draw that stuff up in Access and it does everything for you!"

>.<

[ItsMe]

I would add [Class/Entry].Rider_Number to your combo's SQL statement to aid the User.

There are properties for the Combo that you can adjust. The property sheet will disply them for you. My favs are
Bound Column:
Control Source:
Limit To List:
Column Count:
Column Widths:

You can use the query builder directly with the RowSource property of the Combo. Here is an illustration how to launch it.
https://www.accessforums.net/forms/c...tml#post220068

[June7]

Access can do a lot without code. I have one db with zero code (macro nor VBA). Requires users to become familiar with intrinsic Access tools for filter/sort/record navigation and users must interact with the Navigation pane to open forms/reports, build custom queries. Keep in mind that the more 'user-friendly' the more code.

Change combobox properties to:

Row Source: SELECT Riders.Rider_ID, Riders.Rider_Full_Name FROM Riders;
Bound Column: 1
Column Count: 2
Column Widths: 0";2.0"

[Motoman]

I think I'm picking up what you're laying down...but still have the same "read only" issue. Is there a switch somewhere for that?

[June7]

The issue is possibly with the form properties:

Data Entry
Allow Edits
Allow Deletions
Allow Additions

Or the combobox is bound to wrong field.

Or the form RecordSource - why are you including Class/Entry table?

But not sure.

What is purpose of the form? What is purpose of the combobox? Controls used to input filter criteria must be UNBOUND.


If you want to provide db for analysis, follow instructions at bottom of my post.


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Motoman]

Database1.zip

In general, the purpose of this form (which honestly may be disposable, as I'm really just trying to learn how to do things at this point) is to have a user select a pre-existing rider by name, which would then populate the City and State fields. Then the user would specify a class and a rider number below (class would be a drop-down to select from the Class table, but not worried about that this point). And then that would all save from a not-yet-extant button.

And if a new rider showed up that wasn't in the database, the user would get sent to a different form to input the new rider. Hence the NotInList bits - but again, not worried about that yet.

Right now I am trying to learn how to make the combo box change the values shown in the other cells as you choose different rider names.

Class is it's own table for the sole reason that I've got 3NF on the brain, and have for the last 20 years or so. At some point there'll be a little form just for the Class table so that the user can change what classes are going to be run at a given event. All the action happens in the Entries table - that's where scores go. And where I'll be generating reports from later.

That's basically all there is to this thing. Riders, classes, and scores.

[June7]

You are over-complicating the form RecordSource. A form can enter/edit data for only one table.

Set the form RecordSource to Entries table.

Set the combobox properties:

Row Source: SELECT Rider_ID, Rider_Full_Name, Rider_City, Rider_State FROM Riders;
ControlSource: Rider_ID
Bound Column: 1
Column Count: 4
Column Widths: 0";2.0";1.0";0.25"
Name: cbxRider

Use expressions in textboxes to display the rider info retrieved by the combobox. Set them as Locked Yes and TabStop No.
=[cbxRider].[Column](2)
=[cbxRider].[Column](3)
Column index begins with 0, so the City field is index 2.

Create another combobox for Classes.


Alternatives involve form/subform arrangement:

1. main form bound to Riders and subform bound to Entries with combobox to select class

2. main form bound to Classes and subform bound to Entries with combobox to select rider



All existing records will display. If you want to enter a new record then must navigate to new record row.

If you want to filter the form to a specific rider (or class in case of the last option described), that will be more complicated.