Solved DLOOKUP and multiple criteria and concatenate (1 Viewer)

[Eureka99]

Hi All,

I'm doing a database whereby I'm taking some Manufacturing order details and adding them so we can look quality pass / fail rates and details.

One MO might have anything from 1 to 20 components.

I have linked tour data warehouse to the access file instead of calling it in through SQL (my SQL isnt amazing)

So to explain what I'm trying to do:

Component1 - Counts how many components are on the MO number.
Comp1 - Adds a Zero as the data warehouse stores this as Sequence Number, so component Line 1 is Sequence number 10.

What im trying to do then is loop through the total number of components and add each one to a new line on the components Table - this will hold more data but this area is the sticking point at the moment.

I'm getting a type mismatch on the ![Component] line. Item Number is numberical - 8 digits, although it is saved on the data warehouse as VarChar(15)

Can anyone point me in the right direction?

Component1 = DCount("ItemNo", "Server", "MONo=" & MO1)


For x = 1 To Component1
Comp1 = Component1 & "0"


Dim LastID As String
With CurrentDb
Set rsnew = CurrentDb.OpenRecordset("Tbl_component", dbOpenDynaset)
With rsnew

        .AddNew
        ![Component] = DLookup("ItemNo", "Server", "MONo=" & MO1 And "SeqNo=" & Comp1)
        
        
           .Update
        
        End With
        End With
  Next x

 

[arnelgp]

can you use SQL:

Dim db As DAO.Database
Set db = CurrentDb
db.Execute "Insert Into tbl_component (Component) Select ItemNo From Server Where MoNo=" & [MO1] & " Order By SeqNo;", dbFailOnError
Set db = Nothing

 

[Eureka99]

I can do I guess, But i was hoping to be able to auto fill a couple of lines at a time with extra data.

So for example I will have two linked tables MO Head and component. One such line I wanted to auto insert was the ID field from the MOHead table. The MO Head is being filled using barcode scanning, Input boxes and the same RS .add as above, using a LastID field.

 

[Josef P.]

Component1 = DCount("ItemNo", "Server", "MONo=" & MO1)
...
        ![Component] = DLookup("ItemNo", "Server", "MONo=" & MO1 And "SeqNo=" & Comp1)
...

Find the bug:

dim CriteriaString as String

CriteriaString =  "MONo=" & MO1 And "SeqNo=" & Comp1
vs.
CriteriaString =  "MONo=" & MO1 & " And SeqNo=" & Comp1

 ![Component] = DLookup("ItemNo", "Server", CriteriaString)

 

[Eureka99]

Many Thanks Josef!!

And i found your bug

worked perfectly and exactly what I was looking for.

CriteriaString =  "MONo=" & MO1 & " And " & "SeqNo=" & Comp1

 

[arnelgp]

you don't need Dlookup.

when you do this:

Component1 = DCount("ItemNo", "Server", "MONo=" & MO1)

If Component returns 10, then for sure, these 10 records will have, 10, 20, 30, etc. (based on your first post#1).
and your For..Next will get exactly 10 records?

and using SQL, you will also get Exactly 10 records:

"Insert Into tbl_component (Component) Select ItemNo From Server Where MoNo=" & [MO1] & " Order By SeqNo;"