Access VBA looping Recordset (1 Viewer)

santoshrnath

New member
Local time
Today, 01:13
Joined
Apr 5, 2017
Messages
5
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.

ID table.JPG

mastertable-new.JPG


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.
 
Last edited:

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:13
Joined
Apr 27, 2015
Messages
6,286
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

New member
Local time
Today, 01:13
Joined
Apr 5, 2017
Messages
5
Hi,
Actually the field is there, but was not shown in the screen shot
mastertable-new.JPG
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:13
Joined
Apr 27, 2015
Messages
6,286
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

New member
Local time
Today, 01:13
Joined
Apr 5, 2017
Messages
5
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

Ignore List Poster Boy
Local time
Today, 04:13
Joined
Apr 27, 2015
Messages
6,286
Glad to hear the code is working fine.

Best of luck!
 

santoshrnath

New member
Local time
Today, 01:13
Joined
Apr 5, 2017
Messages
5
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.
 

Attachments

  • Sequencetest.accdb
    560 KB · Views: 385

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:13
Joined
Apr 27, 2015
Messages
6,286
Going to need all forms and queries as well to properly trouble shoot this.
 

Cronk

Registered User.
Local time
Today, 19:13
Joined
Jul 4, 2013
Messages
2,770
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

New member
Local time
Today, 01:13
Joined
Apr 5, 2017
Messages
5
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
 

Users who are viewing this thread

Top Bottom