How to Add a Record From a Form Using Comboboxes

[gicarto]

Hello-

I have searched the web for a whole day trying to figure this out. I give up. I need to build a data entry form that inserts values from other tables using comboboxes. There is only one text box for entering new data. I've tried using the data entry form wizard and I cannot get the comboboxes to work right on that form so I am using a blank form. Here is what I am trying to do.

A. Select a municipality from a combobox in order to change the combobox below that selects the tax district. The municipality will not be inserted into the table.
B. Select the taxing district based on the municipality. The ID will be entered into my table.
C. Select the reporting year and insert into the table.
D. Select revenue or expenditure to control the PE-300 combobox below it. This will not be entered into my table.
E. Select the report topic form combobox and write topic ID to table
F. Enter the amount of the expense or revenue. This will be inserted into the table.

Here is a picture of the form so far. Boxes in red need to be inserted in the table and boxes in green are only for controlling other controls.





I have all of the comboboxes working in terms of setting row sources and re-queries. Now I need to figure out how it actually gets inserted into the table. I have designed an append query but I cannot get it to pull data from the form. It might be a simple setting but I can't figure it out. Do I need a button and a macro?

I am using Access 2016.

Thank you

[ranman256]

use the BUILDER. you can select the items off the form to put in the append query.
in the query, put the cursor in an empty field box on the grid, click the builder (ellipsis wand on toolbar)

usu:
forms!fMyForm!cboMunicipal , forms!fMyForm!cboTID, etc

[CJ_London]

what you are looking for is what is commonly known as cascading combo's.

Assuming your municipality combo is called cboMunicipality and it is bound to the MunicipalityPK column (which is hidden)

the rowsource you need for your TID combo will be something like

SELECT TIDID, TIDNumber FROM tblTIDS WHERE MunicipalityFK=[cboMunicipality]

the only thing you then need to do is refresh the TID control whenever the cboMunicipality changes or when you go to a new record

so in the cboMunicipality after update event put something like

cboTID.requery

and in the form current event put

cboMunicipality_afterupdate


I don't see why you have a problem using a bound form

[gicarto]

Unfortunately, I am even more confused. Do you mean use the Builder on the Append Query design sheet? Or, are you referring to the macro builder? I learned bout the control source property but when I set it, the combobox won't even select right. I think I might be beyond help.

[Gicu]

Why don't you create a stripped down version of your db with any confidential data removed and only a few "dummy" records to illustrate your issue. Run a compact and repair and zip it before uploading. As Ajax said yesterday it would be much easier if the form you use to add data would be bound to tblTIfAnnualReport table (meaning in the record source of the form you have tblTIfAnnualReport or a Select statement based on it if you need any custom sorting - "Select * FROM tblTIfAnnualReport Order By [TID unique ID];"). Next step is to bind each individual control to its corresponding field in the table. And you're done...

Cheers,

[June7]

What you describe is called cascading comboboxes (as indicated by Ajax). However, if you don't save value at each level of combobox heirarchy and comboboxes use alias, data won't display properly when record is revisted on this form. This is because comboboxes are dependent on ID in previous combobox. If ID is not saved, dependent combobox won't know what value to display.

Should not need append query with bound form.