Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    29

    Unanswered: VB to Increase record update by 1

    Hello.

    I have a form with multiple fields on it. We will call them SLO

    They are text boxes and there are 6 of them named SLO1, SLO2.....SLO6

    What I want to do, is use VB to update each as a string to a table individually. But Id like to only type the code once, and use a DO WHILE and increase the value by one.

    Example.

    Current Code looks like this:

    Private Sub UpdateButton_Click()
    Set rstNewInventoryDataRecord = CurrentDb.OpenRecordset("Select * FROM MetricData")
    Set CurrentForm = Screen.ActiveForm


    Dim Counter As Integer
    Counter = 1

    rstNewInventoryDataRecord.AddNew
    rstNewInventoryDataRecord.SLO = CurrentForm.SLOT1.Caption
    rstNewInventoryDataRecord.Update

    MsgBox "Update Complete"
    End Sub


    What I want to do is use the counter to increase by one after each loop and stop after 6.

    So...Do while Counter < 7

    and incease the CurrentForm.SLOT1.Caption by one each time.

    Ive tried

    rstNewInventoryDataRecord.SLO = CurrentForm.SLOT & Counter.Caption
    and
    rstNewInventoryDataRecord.SLO = CurrentForm."SLOT" & Counter.Caption

    as well as a few other ideas all to no avail

    Is this possible? Surely it has to be???

    Suggestions?

    Many Thanks!

  2. #2
    Join Date
    May 2009
    Posts
    29
    Go it all figured out....



    Option Compare Database

    Private Sub UpdateButton_Click()
    Set rstNewInventoryDataRecord = CurrentDb.OpenRecordset("Select * FROM MetricData")


    Dim CurrentForm As Form
    Dim Counter As Integer
    Counter = 1
    Set CurrentForm = Screen.ActiveForm

    Do While Counter < 7
    rstNewInventoryDataRecord.AddNew

    rstNewInventoryDataRecord.SLOCOUNT = Counter
    rstNewInventoryDataRecord.SLO = CurrentForm("SLOT" & Counter).Caption
    rstNewInventoryDataRecord.ValueDateMonth = Month(CurrentForm("SLOD" & Counter))
    rstNewInventoryDataRecord.ValueDateYear = Year(CurrentForm("SLOD" & Counter))
    rstNewInventoryDataRecord.Metric_Value = CurrentForm("SLO" & Counter)
    rstNewInventoryDataRecord.Update
    Counter = Counter + 1
    Loop

    MsgBox "Update Complete"


    End Sub

Posting Permissions

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