Form ListBox Know-how

[InThrees]

My roommate wants me to set up an Access db to track tenant rent payments, since we're all out of work deadbeats who can only really pay piece-meal, or in barter.



He wants a db that will let him enter a payment, indicate whether it is cash or not, and if not, enter some notes to see what sort of arrangement was made. (I.E., $100 'payment' actually consisted of deadbeat roommate creating monthly rental db in Access.)

So I'm looking at:

*Tenants Table*
Tenant ID (Primary Key) - linked to ID in transactions table
Tenant Name
Tenant Rent (monthly rent rate, fixed value)

*Transactions Table*
Transaction ID, primary key
Tenant ID, linked to tenants table -> tenant id
Transaction Date
Transaction Amount
IsCash (yes or no)
Notes (if not cash, then what)


What I CANNOT figure out is the entry form for entering payments. What I would like is a form (called Entry or similar) that lets him select the roommate making a payment, the amount of the payment, whether it's cash, what day it was made, and any notes.

The problem I'm running into is allowing a selection of the roommates name to then translate into looking up the appropriate Tenant ID and then inserting that into the transactions table when the form is filled out. (If that's not clear, please say so and I'll try to be more explicit.)

Or put another way, I'd like to populate a list box with values (all unique) from all records in one table, and then have the form insert a related field from the chosen record into another table.

In other words, the list box shows our names, but selecting a name means our Tenant ID is entered into the transactions table.

[pbaldy]

Your form would be bound to the transactions table. The combo or list box would be bound to the tenant ID field in that table, but its row source would be the tenants table (or a query based on it).

[InThrees]

Ok, thanks to your help I got that part to work, but now on to the next problem - if I doubleclick the form with no records in transactions, I can fill out the form and hit enter and close the form and then see the new information in transactions (i.e., it now has a record with what I just entered.)

However, if transactions has a record then that record populates the form when I open it up again, and subsequent alterations of the data in the form (i.e., let's add a new payment) actually overwrite the old record.

How do I make the form instantiate empty and add a new record when done?

I added a button, labeled it 'Done' and had it close the form when clicked, and the record was added, but then subsequent openings of the form were populated with the record, which was again updated rather than a new record being added.

I could do all this with php and mysql, but access is a monster I'm just not familiar with. =(

nevermind - roommate pointed out the little 'new record' star button in form view, and said that's what he was used to, so nothing elegant is needed here.

[pbaldy]

Another option is to set the form's data entry property to Yes, which will make it open to a new record all the time. You can also use the arguments available to OpenForm to open it in data entry or edit mode, as desired.

[InThrees]

Another option is to set the form's data entry property to Yes, which will make it open to a new record all the time. You can also use the arguments available to OpenForm to open it in data entry or edit mode, as desired.

Thank you again! I will alter the data entry property. You have single-handedly made me think this site is awesome in the span of 12 hours.

[pbaldy]

LOL! Happy to help, and welcome to the site by the way.