my first form (1 Viewer)

[PistolPete]

Apologies if I am using wrong etiquette and/or protocols. Please let me know. I am building my first form. Overall concept is to track parking stalls that I rent out. I have 3 tables tbl_Renters, tbl_stalls, tbl_RentalHistory. I am only working with tbl_Renters for now. I has the basic contact info BillingName, ContactName, Address, etc. I am trying to just build the form to input data. Actually I have populated the fields already with an upload from excel. My 1st from was a combo box that would either populate the text boxes.. BillingName, ContactName, etc when I select the Renter from the combo box. I got the combo box to work but when I select the renter it doesn't populate. So far, I got recommendations to write VBA but to me I would think this should be a basic function of Access. Aren't a great deal of databases designed to track some sort of customer. Anyway I can't get the vba to work. Thanks in advance

 

[theDBguy]

Hi. Have you tried looking at some of the built-in templates in Access maybe just to get an idea on how to build a form that may work for you?

 

[CJ_London]

I suspect you need some unbound controls to display additional columns in your combo rowsource

 

[bob fitz]

Apologies if I am using wrong etiquette and/or protocols. Please let me know. I am building my first form. Overall concept is to track parking stalls that I rent out. I have 3 tables tbl_Renters, tbl_stalls, tbl_RentalHistory. I am only working with tbl_Renters for now. I has the basic contact info BillingName, ContactName, Address, etc. I am trying to just build the form to input data. Actually I have populated the fields already with an upload from excel. My 1st from was a combo box that would either populate the text boxes.. BillingName, ContactName, etc when I select the Renter from the combo box. I got the combo box to work but when I select the renter it doesn't populate. So far, I got recommendations to write VBA but to me I would think this should be a basic function of Access. Aren't a great deal of databases designed to track some sort of customer. Anyway I can't get the vba to work. Thanks in advance

Can you post a copy of your db

 

[arnelgp]

see this demo and open form frmRenters.
see the code behind the combobox.

 

[Gasman]

So far, I got recommendations to write VBA but to me I would think this should be a basic function of Access.

No database will do anything unless you tell it to in some way.

 

[The_Doc_Man]

Apologies if I am using wrong etiquette and/or protocols. Please let me know.

You are doing fine. Since you mention it, though, and you are new to us...

The etiquette "rules" are
(a) no abuse of other members, with or without vulgar language - though we certain can and do make little jokes with each other sometimes.
(b) don't post on another forum to get a second opinion without telling us and posting a link to it. This is called cross-posting and it wastes our time unless we, too, have drawn a total blank.
(c) communicate. Don't say "this doesn't work" but fail to tell us what it DOES do and why that differs from what you expected. We aren't mind readers and I lost my crystal ball while on vacation a couple of years ago.

Protocols include giving us time to think before we answer, since we are literally all over the world from almost every populated time zone. When you post, we might be sleeping. Arnelgp, for example, is from the Philippines. I'm from south Louisiana, USA. Bob Fitz is from or near London. You are from or near Edmunton, Alberta, Canada. We've got folks from every continent except, perhaps, Antarctica. Remember that we are all volunteers, just paying forward the help that we once got - and often STILL get from the other members.

You CAN disagree with us and pursue the disagreement. You CAN tell us you don't understand and ask us for clarification. I'm letting YOU know that you are doing fine so far.

NOW... as to populating multiple fields from a combo box,

ComboBox.RowSource property (Access)

Office VBA reference topic

learn.microsoft.com

So far, I got recommendations to write VBA but to me I would think this should be a basic function of Access.

It is and it isn't. You could in theory bind the combo box to the bound column designated from that box's .RowSource and that would work. The control's .Value property comes through that mechanism. In fact, when you build the combo box with a wizard, that linkage is one of the three choices that the wizard offers you. It can easily set that up for you. However, only one column from the .RowSource can be the "bound column" so copying one column's value is a basic function of Access. Multiple columns are not automatic.

ComboBox.BoundColumn property (Access)

Office VBA reference topic

learn.microsoft.com

The solution might be as simple as remembering that the value of the combo box is selected when you click on that value. Therefore, in your control's controlname_Click event, you could copy the OTHER columns.

ComboBox.Column property (Access)

Office VBA reference topic

learn.microsoft.com

Just remember that in Access and other MS Office members, most collections are zero-based, not 1-based, so the first column is controlname.Column(0) and the last of n columns is .Column(n-1).

 

[Pat Hartman]

Welcome to AWF

What you have is a simple many-to-many relationship. You have renters. You have stalls. At some point in time, a stall is associated with a renter.

This can be handled in at least two different ways. One way uses only code and append queries to update the history table. The other way, uses bound subforms. Small amounts of code might be required but only to validate entries.

The history table needs both a start and an end date.

BUT, the stall can only be associated with ONE renter at a time. This leads us to the code method probably being simpler. So, add the RenterID as a FK to the stall table. (make sure the default for this field is Null and NOT 0) Behind the scenes, when you open a stall to connect it to a renter, the form should only show unoccupied stalls. You use a combo to select a renter and when you save the record, the history record is created using an append query in the form's AfterUpdate event. If the stall is occupied, when you remove the renterID, then the update query sets the PeriodEndDT in the history to Now(). If you want to change from one renter to another, the process is more complicated. For now, I would just force the intermediate step of making the stall unrented and saving the record before renting it after the history record has been updated.

 

[PistolPete]

Hi. Have you tried looking at some of the built-in templates in Access maybe just to get an idea on how to build a form that may work for you?

Hi. Have you tried looking at some of the built-in templates in Access maybe just to get an idea on how to build a form that may work for you?

good suggestion. and I am going to review the videos I watched. But I recall they only showed how to query in the grid at the bottom. thanks

 

[PistolPete]

I suspect you need some unbound controls to display additional columns in your combo rowsource

from my understanding the combo box needed to be unbound because it was to pull more than one field of data. But my RenterID (primary key) is bound but I will have another look at thanks

 

[PistolPete]

Can you post a copy of your db

how do post a copy of the db? thanks for the help

 

[PistolPete]

see this demo and open form frmRenters.
see the code behind the combobox.

I looked at your db and it doesn't seem to populate either. Am I missing something. Yours was a good example and pretty much the same as my table. thanks,

 

[PistolPete]

No database will do anything unless you tell it to in some way.

yes, I've been trying to get it to do something. both with vba and queries. the textboxes jsut don't seem to populate thanks for the help

 

[The_Doc_Man]

from my understanding the combo box needed to be unbound because it was to pull more than one field of data. But my RenterID (primary key) is bound but I will have another look at thanks

Incorrect. The combo box can be bound because it has a .RowSource (to select a row) and it "returns" a .Value, so you can use it to load a bound field (for example). It returns more than one value reachable through VBA via the .Column property, but the .BoundColumn is singular and is the only "automatic" return value.

EDITED by The_Doc_Man for a technical correction and poor choice of phrase. 11/3/24 08:45

 

[arnelgp]

I looked at your db and it doesn't seem to populate either. Am I missing something. Yours was a good example and pretty much the same as my table. thanks,

maybe you forgot to "Unblock" it after you download it.
"Unblocking" will let the VBA work.
here's how to do it:

 

[Pat Hartman]

from my understanding the combo box needed to be unbound because it was to pull more than one field of data.

That isn't the way combos work. Combos (and listboxes) have a ControlSource property. That indicates what field of the current record the selected value will be saved to. The list part of the combo is its RowSource. When the list is expanded, it shows multiple columns. When an item is selected, only the first visible column shows. If you want the combo to show more data when closed, you do it by manipulating the RowSource. So instead of the RowSource being:

Select RenterID, LastName, FirstName From tblRenters

Make it

Select RenterID, LastName & ", " & FirstName As FullName from tblRenters

The RenterID is still bound to the RenterID field of the Stalls table but the combo now shows the concatenated name rather than two separate columns when expanded and only the last name when closed.