Update Query?

[Fireman1143]

Part of my applicaton has a table in which I would like to have the one field - fldCompleted - filled in with a date when a form attached to another table has the Completed Date filled in.

The form has two hidden fields which can be used for matching the table with the fldCompleted. I have been looking at using an OnUpdate event and after discussing this I would like to try to use an Update Query. Several attempts have yeilded nothing but error messages.

Thanks

 

[TheAceMan1]

How are ya Fireman1143 . . . . . . .

Filling in the field is not the problem.

What is your criteria for determining the specific record?. If you can supply this, no problemo . . . . .

TheAceMan

 

[Fireman1143]

The criteria is -

sbtHOIItems.[THHOIRIPRID] = frmWarRepOrd.[fldTHHOIRIPRID]
- a table- - a form-


The field I want to fill in is sbtHOIItems.[HOIComplete]which would receive from frmWarRepOrd.[fldWRComplete]
Both are date fields.

Thanks for the assist!

 

[TheAceMan1]

OK Fireman1143 . . . . . .

In the AfterUpdate Event for fldWRComplete, copy and paste the following code.
Note: Msg = & Title =, are strings provided by you.
Note: The MessageBox is a prompt just incase the table record does'nt exist.
To Test: Enter a date in fldWRComplete and check the record in the table.

   Dim db As Database, rst As Recordset, frm As Form, SQL As String
   Dim Msg As String, Style As Integer, Title As String
   Set db = CurrentDb()
   Set frm = Forms!frmWarRepOrd
   Set SQL = "SELECT TOP 1 sbtHOIItems.[THHOIRIPRID], sbtHOIItems.[HOIComplete] " _
           & "FROM sbtHOIItems " _
           & "WHERE sbtHOIItems.[THHOIRIPRID] = " & frmWarRepOrd.[fldTHHOIRIPRID] & ";"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      Msg = "Your Warning Message If The Table Record Does'nt Exist"
      Style = vbInformation + vbOKOnly
      Title = "Text You Want In The TitleBar Of The Message Box"
      MsgBox Msg, Style, Title
   Else
      rst.Edit
      rst!HOIComplete = frmWarRepOrd.[fldWRComplete]
      rst.Update
   End If
   
   Set rst = Nothing
   Set db = Nothing
   Set frm = Nothing]

And there you have it! If you have any problems let me know, but be sure you check for any typo's of table/form/field names first.

TheAceMan

 

[Fireman1143]

Thanks TheAceMan

I 'll give this a try and let you know.

Fireman1143

 

[Fireman1143]

To The AceMan,

I ran into two things. I needed to change -

Dim db As Database to Dim db As CurrentData

(I,m using Access 2000 and am not sure why Database was not allowed)

Now the error I get is -

Compile Error
Object Required

With Set SQL highlighted.

Any thoughts? Thanks!
Fireman1143

 

[TheAceMan1]

Gotcha Fireman1143 . . . . . . . . .

You did not have to change anything. db, has to reference a Database object!. Hence the error raised by changing it to Dim db as CurrentData. The problem is, your missing a Library Reference!, so lets get it installed . . . . . .

Go back to the form module where you installed the code.
On the MenuBar goto Tools-References.
Scroll down to Microsoft DAO 3.6 Object Library; check off the box, and click OK.
Change Dim db as CurrentData back to Dim db as Database, compile, get back to the Database window, an do your testing.

I await you results . . . . . . .

TheAceMan

 

[Fireman1143]

TheAceMan

Have done the above and reset all coding as previously written.

When leaving the field I get the following -

Compile Error
Object Required

With Set SQL highlighted.

Thanks,
Fireman1143

 

[TheAceMan1]

Im so sorry Fireman1143 . . . . . . my mistake.

SQL is not and object, its a string.

Take out Set so it reads SQL =, instead of Set SQL =.



TheAceMan