Results 1 to 4 of 4
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    If Then Statements with Multiple Tables.


    I am trying to make my Form autofill certain fields.

    Right now, cmbFoodName in my frmFood is linked to tblFoodReference

    After I type the name of the food in cmbFoodName, I want to add an After_Update() event to autofill the remaining fields in myfrmFood. I want the autofill to collect the data from the tblFoodReference table.

    Basically, what I am trying to do is this:



    Private Sub txtFoodName_AfterUpdate()

    If Me.txtFoodName = Table.tblFoodReference.txtnameoffood Then
    Me.txtFoodPrice = Table.tblFoodReference.priceoffood.

    End Sub


    I want Access to autofill the txtFoodPrice with the txtpriceoffood that is associated with the txtnameoffood from the tblFoodReference

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont use IFs ,you use a lookup table.
    the table has the food to pick and the price,
    the afterupdate will run an update query to fill in the other fields from the lookup table to the entry table.

  3. #3
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I see. That actually makes sense to me.

    I have never used an update query with two tables however.

  4. #4
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Some of your question is quite common: without knowing your table structure - this advice is generic - but when you are in a transaction table/form and select an item from a look up table (tblFoodReference) you can get all the fields from that look up table. In the combobox in your transaction form - alter the record source to include all the fields you need - they will display during the drop down selection action.

    Then you can either actually write them into the transaction form/table fields - or - simply have them display in the transaction form (no table involved) in unbound fields. The best manner chosen depends on the nature of data. In both methods one calls these other fields by column i.e. [ComboboxName].column(2) or 3 or 4 etc.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple iif Statements in 1 Exp
    By dmd in forum Queries
    Replies: 16
    Last Post: 12-01-2015, 08:15 AM
  2. Replies: 2
    Last Post: 09-04-2013, 12:11 PM
  3. PROJECT ROV: If statements across tables
    By jrubenol in forum Access
    Replies: 10
    Last Post: 07-01-2011, 10:12 AM
  4. Multiple SQL Statements
    By springboardjg in forum Queries
    Replies: 1
    Last Post: 04-18-2011, 10:32 AM
  5. Multiple IIF statements
    By KevinMCB in forum Queries
    Replies: 4
    Last Post: 12-03-2010, 01:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums