Value Function

[Ninabunny]

I am currently trying to update registrations dates in the brand table according to the registration numbers in the checks table. Basically the checks table has the fields check, date, check amount, reg amount, reg# beg, to, reg# end and the Brands table has Reg #, Brand, Type, Date.
What I want to do is update the dates in the Brand Table for the current record, starting with reg#beg to Reg#end.

Thanks....I seem to be brain dead today...I know it will be a function but need some help correlating the data.

Nina

 

[grtammi]

Hi Nina...

As I understand it, this is what you would like to do:

if the value of Reg # in the brands table is between the reg#begin and reg#end of the checks table, then update the date field in brands table to match that of the checks table; otherwise, leave it alone. Do this function for all records.

Why not use an update query? Or, if you like, create a command button on a form (I'll call it cmdUpdate for example purposes), and insert the update code as follows:

(Note: for clarity purposes, I'm going to use BT as the name of your brand table, and ChkT as the name of your checks table; you'll need to insert proper names later on.)

Private Sub cmdUpdate_Click()

  'declare variables
  Dim dbs As Database, strSQL As String
  
  'set database variable 
  Set dbs = CurrentDb
  'create SQL statement:  Update Brands Table by setting the
  'Brand Date equal to the Check Table Date Where the reg. 
  'value in the Brand table falls between the reg value in
  'the Checks table
  strSQL = "UPDATE BT SET BT!Date = ChkT!Date WHERE " _
    & "BT!Reg# BETWEEN " & ChkT!Reg#Begin & " AND " _
    & ChkT!Reg#End & ";"
  'SQL Statement created - execute it using dbs variable
  dbs.Execute strSQL

  'Clear all variables
  Set strSQL = Nothing
  Set dbs = Nothing

End Sub

Hope this helps Nina - if this is not what you needed, I hope this small piece of code puts you on the right track for what you do need.

Greg Tammi, ATS Alarm Supervisor
E-mail: gtammi@atsbell.com

 

[Namsha]

I think it will but this is in a subfrm so I will have to change the names to match! Will try and be back!! Thank you so much! I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[Ninabunny]

I am stuck on the variant type....Dim db AS Database!....Should I be using ADO?

 

[jhall156]

Sorry to butt in. I believe you want to add the reference for Microsoft DAO (data access objects) in your code window. JHall

 

[Ninabunny]

Oh Please butt in I really appreciate all help! Can you give me the reference...I am not feeling well today.

 

[jhall156]

In your code window, Tools|References
check box for Microsoft DAO 3.51 or 3.6

If you've done it correctly you should get intellisense (the automatic feature) when you type this in your general section of the code
Dim MyDB As DAO.Database

Then you can in procedure

Set MyDB = DBEngine (0) (0)
etc.

Let me know if you have problems.
JHall

 

[grtammi]

*agrees with JHall*

 

[Ninabunny]

Looks good just don't or rather haven't ever programmed that way before....sorry.

 

[jhall156]

No apology necessary. Every day, a bit farther... JHall

 

[Ninabunny]

I guess it is back to the ol' drawing board...hafta look it up and copy the database so I don't screw things up. Thanks again guys!