update multiple records

[garymilam]

I have a date closed field on a form. How do I make this field update multiple records. For example. I have books database. I have multiple books checked out with a date. I want to update the checkout date from my checkout form on multiple records which is a continuous form. Please help

[garymilam]

I have attached my DB. I need to update all the records that have been selected for checkout with the checkout date from above. Somebody please help, I need this for my job. Thanks.

[PhilOfWalton]

Firstly, I think your FPrint field should be a Y/N field, not a text box. This would change the form to having a check box.
So you would select the books that you want to apply the checkout date to by "ticking" the check box.
Secondly you need a Command Button on your form that would then run the routine to update the records with the checkout date.

Questions:

What do you want to do if there is already a checkout date against a book?

How many books are there: is the list going to be impossibly long?

Phil

[jforbes]

There are a few different ways to go about this.

One is to use a RecordSetClone of your Form and iterate through all the records, and update the Date. Here is an example that updates an Integer field:

    Dim oRst As DAO.Recordset
    Dim iCount As Integer
 
    iCount = 0
    Set oRst = Me.RecordsetClone
    oRst.MoveLast
    oRst.MoveFirst
    Do While Not oRst.EOF
        iCount = iCount + 1
        oRst.Edit
            oRst!PrintIndex = iCount
        oRst.update
        oRst.MoveNext
    Loop

Another is to build up a SQL Update Statement in VBA and then call CurrentDB.Execute:

    Dim sSQL As String
    If Len(Me.Filter) > 0 Then
        sSQL = ""
        sSQL = sSQL & "UPDATE Drawings SET LastModifiedDate=#" & Now() & "# "
        sSQL = sSQL & "WHERE " & Me.Filter
        CurrentDB.Execute sSQL
    End If

[garymilam]

Basically the check out date in my real dB is a destruction date. I would ultimately like to have that record archived somehow once there is a destruction date. as far as records, eventually there will be anywhere from 0-200 give or take. I really appreciate the help, I'm still kind of a novice at this, and can use all the help I can take. I really enjoy designing databases and want to learn all I can.

[NeoPa]

I've merged these two threads as we forbid double-posting of questions here.

Unfortunately, neither attempt seems to be well enough expressed to make good sense on its own. Hopefully together they do.

@Gary.
It's your responsibility to ensure your questions are clearly laid out when you post your question originally. If, as you say, it's important for your job, then it's particularly important for you. Poor and lazy questions are far less likely to get sensible answers as the experts will struggle to match the answer to the question.