Update Combobox after new record is added

[Procman]

ACCESS 2003 - The basic idea is to input each individual item from receipts/invoices into the Receipts form which has the Items subform as a datasheet. There is a Receipts table and an Items table linked by the Receipt number. In the Items subform, the Item is a combo box with each unique item from the table. an After_Update Event enters the appropriate information in the next 4 fields and moves to the Debit field, based on the item entered/selected. This Db is used as a single data entry point for all receipts and invoices and Excel is used to analyze the data. I started out using Select Case, but there must be a new Case added for each new item. I know there is a simple way to look this up, but I have not been able to find it. I am specifically trying to have the combo box requery the Items table after each record is entered in the Items subform, so if there are many of the same new item it will now show in the drop down when adding the next record. I used Requery, but it does not update the list until the Receipt record changes. I want it to update as each Item record is added. The code below shows the 4 fields that are entered based on the item selected. Thank you for any and all assistance.

Private Sub Item_AfterUpdate()

Select Case Item
    Case "Apple Juice"
        Category = "Food - Groc - Food - 03 - AJ"
        CatGroup = "Grocery Costs"
        FMBGroup = "Food"
        FMBLineItem = "Food"
        Debit.SetFocus
        
    Case "ATT Uverse"
        Category = "Bills - Uverse Account Charges"
        CatGroup = "Other Bills"
        FMBGroup = "Housing"
        FMBLineItem = "Other Bills - AT&T Uverse Account Charges"
        Debit.SetFocus
        
    Case "ATT Wireless"
        Category = "Bills - Cellular"
        CatGroup = "Telephone Bill"
        FMBGroup = "Other Bills"
        FMBLineItem = "Cell Phones"
        Debit.SetFocus

[zmbd]

What you are doing is a little hard to follow...

However, on first blush, it sounds as if you need a table that has this information. You then build the logic based on the lookup from the table. Please take a look at: > Database Normalization and Table Structures. Right now, you are building your table and the relationships in VBA hardcoding.

As for the control:
The combo/list box has a "control source" property and a "row source" property. These can be set to the same record set; however, these are not the same nor interchangable properties.

Depending on how this control is setup, depends on how we need to look at updating its rowsource. So having the information about how these properties are currently set for the control would be helpfull. So if you would please open the form in the design view and look at the Data tab of the properties for the combobox
Please provide the information for the following:
Control Source
Row Source (if this is an embedded SQL please format using the [CODE/] formatting option)
Row Source Type
Bound Column

Under the format tab:
Column Count
Column Widths
Column Heads

[Procman]

I guess you answered both questions I posted (1 for Access and 1 for Excel). Sorry for the confusion. I guess I asked 2 questions in one post. I wanted to attach both the DB and Workbook so you can see exactly what I'm trying to do, but I cannot upload the mdb file. The purpose of these files is for budgeting and reporting for home and home business use. The DB is used for entering all receipts and invoices and the workbook is used for analysis and reporting. Income is entered into the workbook and the expenses in the DB. The FMB worksheet is the report and I will be working more with the data from there (i.e. charts, etc). I want this automated so that the (non-tech) user will have to do is enter the receipts/invoices in the DB (Receipts Main Form) and Paychecks/Income in Excel (Wages tab), then be able to view the current data in the FMB and YMC-B worksheets. Each month will be a new FMB sheet and the YMC-B sheet is for the year.
The specific combo box question is about having the Items drop down refresh the list after each record is entered in the Items subform, instead of after moving to a new Receipt record.
The 2nd question I had was about automatically filling in the Category and FMB fields after selecting an Item from the drop down list, without having to create a separate Case for each item. Instead it would simply be read from the Items table.

I've attached a Word doc with print screens of the form and drop down list. This is the code to requery the list (which is in Form_ItemsSubfo rm code):

Option Compare Database
Private Sub RequeryList()

    Dim ctlCombo As Control

    ' Return Control object pointing to a combo box.
    Set ctlCombo = Me!Item
    ' Requery source of data for list box.
    ctlCombo.Requery

End Sub

The problem is that drop down list does not show any new items until after the Receipt record changes.


The code for the Select Case question was in my first post.

[Procman]

Oops...I forgot to include the PrtScr of the combo box properties.

[zmbd]

Procman
Due to IT constraints at work, I do not normally download attachments. Would you mind pulling your screen shot out of the document and uploading to the thread using the [advanced] button.

IMHO: You are making this WAY too hard for anyone to use your application.

Dump one product and either go with Access or go with Excel.

I think you'll find the Access better suited to handling the task and in fact, there should be a few templates out there on the net that will handle what you need... free for the asking. The commercial book-keeping programs use a database engine for this job too.

IF you have to have the Excel report, this can be generated as I've noted in your other thread. Post#2- access to summary worksheets. However, once again, I point out that Access should be able to provide these reports as well.