Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: update many text fields based on a combo box if statment required??

    Hi,
    my name is andrew I am doing this as a favour to my boss, and have limited access exp, can some please help me!??
    I have a combo box (productname) the rowsource is based upon another combo box (categoryname), works no problem, with a query. 'after update' on the productname I have this

    Private Sub ProductName_AfterUpdate()
    Me.fooditem1 = Me![ProductName].Column(1)
    Me.calories1 = Me![ProductName].Column(2)
    End Sub

    this also works the 2nd combo box (productname) selection send the info to 2 fields on the form. want i need to do update many fields based on consecutive choice, to specify that :

    if the fooditem1 field and the calories1 already have a selection from the combo box (productname). then the next (productname combo box) choice will update the next two field on my form fooditem2 and calories2.
    i have 10 food fields and calorie fields in my form, and i dont want 2 combo boxes corresponding to each of the 10 fields, but want the combo box productname to cascade down and update all fields that are null

    any ideas!? i have a feeling an iif null then update next field type of statmenet but dont have much idea what im doing

    thankyou thankyou thankyou to anyone that replies

    andrew

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use an update query, something like this:
    Code:
    Private Sub ProductName_AfterUpdate()
    
        Dim varFoodItem as Variant
        Dim varCalories as Variant
        Dim strSQL as String
    
        varFoodItem  = Me![ProductName].Column(1)
        varCalories  = Me![ProductName].Column(2)
        strSQL = "UPDATE <Source> SET <Source>.<FoodItem> = varFoodItem WHERE <FoodItem> Is Null"
        CurrentDb.Execute strSQL, DbSeeChanges
        strSQL = "UPDATE <Source> SET <Source>.<Calories> = varCalories WHERE <Calories> Is Null"
        CurrentDb.Execute strSQL, DbSeeChanges
    '
    ' OR: (All in one, provided both columns can/must be updated together)
    '
        strSQL = "UPDATE <Source> SET <Source>.<FoodItem> = varFoodItem,  <Source>.<Calories> = varCalories WHERE (<FoodItem> Is Null) AND (<Calories> Is Null)"
        CurrentDb.Execute strSQL, DbSeeChanges
    
    End Sub
    Where <Source> is the name of the table or the query that is the RecordSource of the form and where <FoodItem> and <Calories> are the names of the columns to update.

    Have a nice day!

  3. #3
    Join Date
    Sep 2009
    Posts
    3

    update many text fields based on a combo box

    Hi Sinndho
    thankyou so much for your reply, I choose your 2nd option as both columns 1 and 2 from the combo box need to update together, text field 1 and 1 and
    then if not null text field 2 and 2 etc. looks like this:

    Private Sub ProductName_AfterUpdate()

    strSQL = "UPDATE [calender query] SET [calender query].[FoodItem] = varFoodItem, [calender query].[Calories] = varCalories WHERE ([FoodItem] Is Null) AND ([Calories] Is Null)"
    CurrentDb.Execute strSQL, DbSeeChanges

    End Sub

    I have put as the source the query that my form is based upon... but I am getting a runtime error 3061 too few parameters. expected 6, it is highlighting:

    currentDb.execute strSQL, DbSeeChanges

    just to reiterate I want a combo box with 2 columns to update 2 text fields on my form, if those text fields are not null the selection moves to the next 2 text fields, thereby I create a list of choices on my form derived from the combo box. fields are fooditem1, fooditem2, etc and calories1, calories2 etc
    combo box is called 'productname' and has two columns. Form is called 'day'
    and based on 'calender query'

    OMG I have no idea what to do!!! any help is really appreciated

    please help me

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •