Table access 1

[ncopeland]

Hi

I have a database with a number of tables in it. I want to read through one of the tables and check a number of values in each of the records. Does anybody know where I would get examples of code to read through records in a table.

KR

NC.

 

[JoeAtWork]

Google for "ADO recordsets" or "DAO recordsets".

Or tell us more precisely want you want to do and we may be able to give a precise answer.

 

[jedraw]

JoeAtWork,

Here's a function that might be close to what you need:

Function fjCntRecsPrm(sTableNameParm As String)
'
'This function reads all tables in the current database that
'meet the selection criteria (Named whatever parameter is passed) and
'presents an info message of the number of records currently
'in each table


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As TableDef
Dim ltblrecs As Long
Dim sTblName As String
Dim sCountMsg As String

Set db = CurrentDb

For Each tdf In db.TableDefs
sTblName = tdf.Name
DoCmd.Hourglass True
If Left(sTblName, 4) = "MSys" Or _
Left(sTblName, 1) = "~" Then GoTo GetNextrecord
'
If Left(sTblName, Len(sTableNameParm)) <> Left(sTableNameParm, Len(sTableNameParm)) Then GoTo GetNextrecord
'Count the records being transferred
Set rst = db.OpenRecordset(sTblName)
ltblrecs = 0 'set record counter to 0
If rst.BOF And rst.EOF Then GoTo JumpPastCount 'if there is NO data
rst.MoveLast 'if there are records go to the end
ltblrecs = rst.RecordCount 'count the records for transfer
JumpPastCount:
sCountMsg = sCountMsg & sTblName & " " & ltblrecs & vbCrLf
rst.Close
Set rst = Nothing
GetNextrecord:
'sCountMsg = "No Tables meet selection criteria" COMMENTED OUT Sep 4 2001 JED
Next
DoCmd.Hourglass False

MsgBox sCountMsg, vbInformation, "User Tables - Record Counts" & " " & Now()
Debug.Print sCountMsg



End Function

 

[ncopeland]

Hi all

Not quite what I meant but will give the example. I have a table called costingrun. This table will be written to during this procedure.

First of all the table should be opened. The table contains three fields(Year, Period and Costing Date).

I want to read through the table until I get to the EOF. I want to compare the value from a combo box to the value that is in each record for the year field. I also want to compare combo box 2 again from the form against teh period field from the same record. If period and year in each record do not exist then I want to update the three fields with the values from the combo boxes and progres on with processing.

KR

NC.

 

[MajP]

I do not really understand what you are checking

missing either Year OR Period?
missing Year AND Period?
No record for that year and period?

Anyways this should likely be done with SQL. Something like this

Public Sub updateTable()
  Dim strSql As String
  Dim theYear As Integer
  Dim thePeriod As Integer
  Dim theCostingDate As Date
  theYear = Me.cmboYear
  thePeriod = Me.cmboPeriod
  theCostingDate = Me.cmboCostingDate
  strSql = "UPDATE table2 SET table2.Year = " & theYear
  strSql = strSql & " , table2.Period = " & thePeriod
  strSql = strSql & " , table2.CostingDate = " & theCostingDate
  strSql = strSql & " WHERE table2.Year Is Null AND table2.Period Is Null"
  DoCmd.RunSQL strSql
End Sub

 

[JoeAtWork]

I agree with MajP, no need to cycle through all the records just to check if something exists - you can do that in one shot by writing an SQL SELECT statement with a WHERE clause.

 

[ncopeland]

Hi All

I am trying to check to see if a costing run has been done for a set period and a set year. Ie 01 2007. I need to check to make sure this costing run has not been done and if it has to stop the user from running this costing again. f the costing run has been done then I need to write the period and the year and the costing date to the table.

KR

NC.

 

[MajP]

If I understand correctly, something like this. If the period and year do not exist already then add a new record with the period, year, and costing date. If the period and year exist in a record then return a message telling them that there already exists a record with that period and date.

Private Sub Command8_Click()
  Call updateTable
End Sub

Public Sub updateTable()
  Dim strSql As String
  Dim theYear As Integer
  Dim thePeriod As Integer
  Dim theCostingDate As Date
  theYear = Me.cmboYear
  thePeriod = Me.cmboPeriod
  theCostingDate = Me.cmboCostingDate
  If Not DCount("Period", "table2", "Year = " & theYear & " AND Period = " & thePeriod) = 0 Then
    MsgBox "Period and Year already exist"
    Exit Sub
  End If
    strSql = "INSERT INTO table2 ([Year],[Period],[CostingDate])"
    strSql = strSql & "SELECT " & theYear & ", " & thePeriod & ", " & theCostingDate
    'Debug.Print strSql
    DoCmd.RunSQL strSql
End Sub

 

[ncopeland]

Hi All

I also have a further query with regards to reading from tables. I have a table called works order. In it there are 3 fields that need checked and updated.

First field is workscompletion (Yes/No).
Second field is costed (Yes/No).
Third field is costeddate (Date).

I want to read through the works order table and check to see if workscompletion is a "tick"(-1) and costed is a (0). If both these conditions are met then set costed = -1 and costeddate equal to todays date.

KR

NC.

 

[MajP]

build a update query:

UPDATE workorders SET costeddate = Date()
WHERE (((workscompletion)=Yes) AND ((costed)=No))

 

[PHV]

I'd use this SQL:
UPDATE workorders SET costeddate=Date(), costed=True
WHERE workscompletion=True AND costed=False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886