Updating a field in a table

[primagic]

Hi

I am trying to update a field in a table with the following code but I get the error. Two few arguments: expected 1

Here is my code

Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    Set db = CurrentDb()
    strSQL = "SELECT NextChase FROM tblChaseSummary WHERE ChaseSummaryID=Forms!frmChaseSummary!frmChaseDetails!ChaseSummaryID"
    
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

    With rst

    If .RecordCount > 0 Then

      .MoveFirst

      .Edit

      !NextChaseDate = Forms!frmChaseSummary!frmChaseDetails!NewChaseDate

      .Update

   End If

   End With

Help please

 

[Remou]

When you are using SQL strings in VBA, you must use the value from the form, rather than a reference, so:

strSQL = "SELECT NextChase FROM tblChaseSummary WHERE ChaseSummaryID=" _
& Forms!frmChaseSummary!frmChaseDetails!ChaseSummaryID

If the field you are updating is text or date, you will need delimiters.

http://lessthandot.com

 

[primagic]

Hi Remou

Thanks for your reply

Added that SQL code, now i get the error

Item not found in the collection?

 

[Remou]

The reference is incorrect, you need to refer to the form property of the subform control:

& Forms!frmChaseSummary!frmChaseDetails.Form!ChaseSummaryID

http://lessthandot.com

 

[primagic]

same error.

Is there another way of doing this?

 

[Remou]

You could use an update query, rather than a query and a recordset.

In the SQL you refer to NextChase
In the recordset you refer to NextChaseDate

You must select all the fields that you wish to update.

http://lessthandot.com

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jebry]

Hi, there are a few things we need to find out:

"SELECT NextChase FROM tblChaseSummary WHERE ChaseSummaryID=" _
& Forms!frmChaseSummary!frmChaseDetails!ChaseSummaryID

In the statement above:
is NextChase a field in tblChaseSummary?
is ChaseSummaryID as field in tblChaseSummary?
is frmChaseDetails the name of the subform control on the form frmChaseSummary, or is it the name of the subform itself?
is ChaseSummaryId the name of a text box on the subform or is it the name of field that the text box is linked to?

If the answer to either of the first two questions is no then you need to fix the reference to an actual field in the table.

For the last two cases, you must be using the name of the controls rather than the names of the form or field that the control is linked to.

hth




Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[primagic]

The NextChase field is in tblChaseSummary
ChaseSummaryID is the PK in the tblChaseSummary

frmChaseDetails is a sub form of frmChaseSummary which is linked together by ChaseSummaryID

so in the subform, there is a field which is named NewChaseDate.

So what I want it to do is use that date and update the NextChase field in the tblChaseSummary.


My whole purpose is, someone makes an investment and you need to chase them up on a regular basis, so what the chase details should so is record when the customer was contacted and the next time to chase them up. so this field will always be the last date contacted plus the service level (days) which is calculated elsewhere.

so if someone contacts a customer on 01/01/2008 and the service level is 3 days, then the nextchasedate will be 04/01/2008.

Then when they are contacted on the 04/01/2008, this will become the last chased date and the next chase date will be 04/01/2008 + 3 days = 07/01/2008

 

[jebry]

Hi!

Let me be clearer.

You have a main form called frmChaseSummary, you have a subform called frmChaseDetails. To put the subform onto the form, you used a subform control. This control also has a name and it is probably different than the name of the subform. Also, you use a text box control to display the contents of a field. Often the text box control has a different name than the field whose contents it displays. To access a text box on the subform you need to use the following format:

Forms!FormName!SubformControlName.Form!textboxname

The code you have above looks like it uses

Forms!FormName!SubformName.Form!fieldname

which is wrong. If you are using the wrong name for either the subform control or the text box on the subform Access will give you the error you are getting:

Item not found in collection.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst