Query to auto populate another field in same record

CLCoop

New Member
Form called Input Allotments that shows the current funds of one state.
Subform that shows all that states transactions: master_data_auto_fiscal_year_subform based on a Query.
Query: Master_Data_auto_fiscal_year it has all the fields from the MasterData table
The query takes the date entered and breaks it down to Monthnum field, then a field called: FY: IIf([monthnum]<10,Year([DATE]),Year([DATE])+1) this gives me the fiscal year in field FY.

How do you get FY to auto populate FiscalYear field?
 

Joe4

MrExcel MVP, Junior Admin
Last edited:

CLCoop

New Member
FieldMonthnum: Month ([Date])FY: IIf([monthnum]<10,Year([DATE]),Year([DATE])+1)DateFiscalYearREVISED_ALLOTMENTS
TableMaster_DataMaster_DataMaster_Data
SortYESYesYes
Show
Criteria[CURRENT_ALLOTMENT]+[GS_INCREASE]-[GS_DECREASE]+[SB_INCREASE]-[SB_DECREASE]+[LEASING_INCREASE]-[LEASING_DECREASE]
or:[FY]

<tbody>
</tbody>

Nothing happens but asks me what I want FY to be when you run the query.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
You have to have a field pre set-up in your table to hold this value to hold this value (FY).
Then in your Update Query, you would place the calculation on the "Update To" line of this FY field.
If you look closely at the link I showed you, it shows you some examples and some example calculations.

You probably will also want to add criteria under this FY field to only update records where this field is empty (Is Null), so if you run this query multiple times, you are only updating new records that have a blank in this field.
 

CLCoop

New Member
Ok I was able to update records based on Parent Child just had to manually type in each field I wanted to do. As well as there is a limit to how many parent/child relationships but it was enough to "auto fill" what I was looking for.

Go to the form, edit mode, click on the sub form you are wanting to auto fill based on form. go to Data. See the Link Master Fields and Link Child Fields. When you select this you get three boxes to link up. You can however add more fields by putting a ; with now spaces after the last field listed and keep adding. Remember to add the same to both Parent and Child if you skip one you may have data in the wrong field.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top