Access VBA looping Recordset

[santoshrnath]

I have two tables, ID table and Mastertable. In ID table i have level and activity which is given a start number, let us say 1000. In Mastertable i have many drawings associated to the same activity and level. I have to provide numbering to them starting from the ID table value 1000 and increment it by 1. After finishing, the max value from the mastertable has to be replugged to ID table.


Also, if there is Level and Activity, the ID has to be picked looking up for level and activity both, if no level is mentioned then it has to only lookup activity.

I tried a lot but could not succeed. Please help.






I used the Code below, but it looks up only Activity and not level.

Option Compare Database
Option Explicit

Public Function SequenceNew()
Dim strSQL As String
Dim db As Database
Dim rs As DAO.Recordset
Dim a, initNo As Integer
Dim b As Integer


strSQL = "SELECT * FROM MasterTable ORDER BY LevelID"
'Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF

rs.Edit
If rs![DrawingTypeName] = "Concrete" And rs![ProjectName] = Forms!frm_Publish!CboProject And rs!IDGiven = "Not Given" Then
a = a + 1
rs!Sequence = DLookup("CONCRETE", "Qry_ID_Selected") + a

ElseIf rs![DrawingTypeName] = "Reinforcement" And rs![ProjectName] = Forms!frm_Publish!CboProject And rs!IDGiven = "Not Given" Then
b = b + 1
rs!Sequence = DLookup("REINFORCEMENT", "Qry_ID_Selected") + b

ElseIf rs![DrawingTypeName] = "Steel structural works" And rs![ProjectName] = Forms!frm_Publish!CboProject And rs!IDGiven = "Not Given" Then

End If
rs.Update
rs.MoveNext
Loop
rs.Close
Set db = Nothing
Else
MsgBox " No records Found"
rs.Close
Set db = Nothing
End If

End Function

There should be some other better way to do it.

 

[NauticalGent]

Good morning, without getting too far in the weeds at this time, I did notice that the is no field in your RS named "IDGiven".

Try correcting the RS field names and see what results you get...

 

[santoshrnath]

Hi,
Actually the field is there, but was not shown in the screen shot

 

[NauticalGent]

I suspect your DLookup syntax may be the problem.

The way it is written has it looking in a table named CONCRETE and REINFORCEMENT and adding whatever number that is in the QRY_ID_Selected field to either a or b.

Assuming the tables are correct, I see two possible issues:

1. The value in QRY_ID_Selected is not an integer (you would have received a datatype error if this is the case.

2. Because you didn't specify any lookup criteria, the DLookUp function I going to stop at the first record that meets the criteria, which is probably the first record in the table. If there is only one record then there is no issue. If there are more than one record then you are not going to get the results you are looking for.

 

[santoshrnath]

The code works fine, but it only looks for activity. It does not check level. Also it does not update back the max value from Mastertable to ID Table.

 

[NauticalGent]

Glad to hear the code is working fine.

Best of luck!

 

[santoshrnath]

I have attached a test file, i want the sequence from ID table to Mastertable. And update the master table max ID value to ID table.

 

[NauticalGent]

Going to need all forms and queries as well to properly trouble shoot this.

 

[Cronk]

To me it looks like a more basic issue - table design. When multiple records as well as more than one table have the same data, there has to be design flaws.

 

[santoshrnath]

Hi All, There was no design flaw. It was just VBA coding Flaw which i resolved it myself.

Thanks for all your response

the code used is

Option Compare Database
Option Explicit

Public Function SequenceNewLevel()
Dim strSQL As String
Dim strSQL1 As String
Dim db As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset2
Dim a, initNo As Integer
a = 0
strSQL = "SELECT * FROM MasterTable"
strSQL1 = "SELECT * FROM ID"
Set db = CurrentDb
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do While Not rs1.EOF
rs1.Edit
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
If rs![TypeOfDrawing] = rs1![Activity] And rs![Project] = rs1![Project] And rs![LevelName] = rs1![Level] And rs![IDGiven] = "Not Given" Then
a = a + 1
rs!Sequence = rs1!StartID + a
rs1!StartID = rs!Sequence
a = 0
rs![IDGiven] = "GIVEN"
End If
rs.Update
rs.MoveNext
Loop
End If
rs1.Update
rs1.MoveNext
Loop
rs1.Close
rs.Close
Set db = Nothing
Else
MsgBox " No records Found"
rs.Close
Set db = Nothing
End If
End Function


Public Function SequenceNewWithOutLevel()
Dim strSQL As String
Dim strSQL1 As String
Dim db As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset2
Dim a, initNo As Integer
a = 0
strSQL = "SELECT * FROM MasterTable"
strSQL1 = "SELECT * FROM IDWithoutlevel"
Set db = CurrentDb
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do While Not rs1.EOF
rs1.Edit
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
If rs![TypeOfDrawing] = rs1![Activity] And rs![Project] = rs1![Project] And rs![IDGiven] = "Not Given" Then
a = a + 1
rs!Sequence = rs1!StartID + a
rs1!StartID = rs!Sequence
a = 0
rs![IDGiven] = "GIVEN"
End If
rs.Update
rs.MoveNext
Loop
End If
rs1.Update
rs1.MoveNext
Loop
rs1.Close
rs.Close
Set db = Nothing
Else
MsgBox " No records Found"
rs.Close
Set db = Nothing
End If
End Function

 

[Frothingslosh]

Hi All, There was no design flaw.

And the gauntlet has been thrown.

 

[NauticalGent]

And the gauntlet has been thrown.

Going to let that one go!