Sequential line numbers to table 2

[dr1256dr]

Greetings

Seems like this should be easy, but I am stuck!

I import data in a table from another program. I link to the table using ODBC, and use an Append Query with 6 of the fields. The primary key in my new table is multi-field; fieldA, fieldB, fieldC.

fieldA is a "Job Number", in text format. fieldB and fieldC are number fields. fieldD, fieldE, and fieldF are "width", "length", and "date".

Source example;
"43831C", "123", 10, "24.75", "32.25", 05/15/04
"43831C", "175", 18, "28.00", "18.75", 05/18/04
"43914B", "241", 12, "16.25", "32.50", 05/15/04
"43914B", "167", 18, "13.25", "32.50", 05/17/04
"43914B", "058", 12, "19.25", "32.50", 05/15/04

There are anywhere from 1, to 50 records having a unique "Job Number", ("fieldA").

I want to create a new table in my database, for use throughout my application. I would use an Append Query, or VBA code, to update the table at startup. I will sort on fieldD, ascending.

The new table would contain all 6 fields, plus another field; "fieldLineNo". The value for that field needs to be 1 for the first record based on my sort, then 2, 3, etc until the value of fieldA changes. Then the consecutive numbers start over. The last field is my "fieldLineNo"


Desired result:
"43914B", "167", 18, "13.25", "32.50", 05/17/04, "1"
"43914B", "241", 12, "16.25", "32.50", 05/15/04, "2"
"43914B", "058", 12, "19.25", "32.50", 05/15/04, "3"
"43831C", "123", 10, "24.75", "32.25", 05/15/04, "1"
"43831C", "175", 18, "28.00", "18.75", 05/18/04, "2"

I searched all thoroughly amongst the posts, but could not find what I needed, (or understood)! Another forum pointed me towards using a query expression, (Nz(DMax("fieldLineNo","tblResult","fieldA='" & [fieldA] & "'"),0)+1) But I can't see how that would work. I assume I need to use some VBA, possibly with a loop?

Dim db As Database
Dim rs As Recordset
Dim lngLoop As Long
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblMySource")
lngLoop = 1
Do
rs.Edit
rs!ListLineNum = lngLoop
rs.Update
lngLoop = lngLoop + 1
rs.MoveNext
Loop Until rs.EOF
rs.Close
Set rs = Nothing
Set db = Nothing

Set MyRS = Nothing
Set conn = Nothing

Obviously will sequentially number ALL the records, can I
modify that to reset at a new "fieldA" value?

An Access report handles this quite easily, but I need it in a table.

Can anyone help me out here? I am sort of new to Access and VBA, but I can usually get stuff working with a little push in the right direction.

Thanks,

Dennis

 

[scriverb]

Use this VBA code to initially update your file with the line number values.

 Dim db As Database
    Dim rs As Recordset
    Dim lngLoop As Long, [red]txtJobNumber as String[/red]
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("tblMySource")
    lngLoop = 1
    Do
      [red]If rs!("Job Number") <> txtJobNumber then
         lngLoop = 1
         txtJobNumber = rs!("Job Number")
      end If[/red]
      rs.Edit
      rs!ListLineNum = lngLoop
      rs.Update
      lngLoop = lngLoop + 1
      rs.MoveNext
    Loop Until rs.EOF
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    Set MyRS = Nothing
    Set conn = Nothing

You will also have to determine a way to put the next line number value for the Job Number in new records. I will post that next.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Here is an example of the code to use as the Default value for the field ListLinNum.

EXAMPLE:

=DMax("[ListLinNum]", "[red]tblMySource[/red]", "[Job Number] = '" & Me![Job Number] & "'") + 1

This code will find the max ListLinNum for all of the records with the "[Job Number]" equal to the forms control with the same name and increment by 1.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Just thought of a problem. When entering a new record this expression will try to execute as soon as you enter the first character in the Job Number control. This won't work. So, let's remove it from the Default Value property and put it in the AfterUpdate event Procedure of the Job Number control. Then after the Job Number is entered the calculation takes place and the value is assigned to the ListLinNum control.

Me![ListLinNum] = DMax("[ListLinNum]", "tblMySource", "[Job Number] = '" & Me![Job Number] & "'") + 1

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dr1256dr]

Bob

Thanks for the response. I am still on part 1 of your reply.

I am getting an error at run-time; "Type-declaration character does not match declared data type", at this line:

If rs!("Job") <> txtJobNumber Then

My field name in the table is Job, not Job Number or JobNumber. I know my Job field is text. My scope is private function.

Where am I going wrong?

Thanks for you patience.

Dennis

 

[dr1256dr]

Bob

After further reading of your post, I should ley you know that this table will not be update by any forms. I pull the data from elsewhere, and use it to print work orders, and in some labor collection procedures, (barcoding). I also print barcode labels, which is where I use the line numbers. It has to match other paper work coming from the original source program, (A MRP solution software.)

If I can just get the function to assign the line numbers as originally explained, I will clear the table and re-populate it at my apps startup.

So, thanks for all the help, so far!

Dennis

 

[scriverb]

Try this:

 Dim db As Database
    Dim rs As Recordset
    Dim lngLoop As Long, [red]txtJob[/red] as String
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("tblMySource")
    lngLoop = 1
    Do
      [red]If rs!("Job") <> txtJob[/red] then
         lngLoop = 1
         [red]txtJob = rs!("Job")[/red]
      end If
      rs.Edit
      rs!ListLineNum = lngLoop
      rs.Update
      lngLoop = lngLoop + 1
      rs.MoveNext
    Loop Until rs.EOF
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    Set MyRS = Nothing
    Set conn = Nothing

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dr1256dr]

Same type declaration error, same place.

I must be overlooking the obvious, sorry! My complete code:

Private Function AssignLineNos()

Dim db As Database
Dim rs As Recordset
Dim lngLoop As Long, txtJob As String
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblMySource")
lngLoop = 1
Do
If rs!("Job") <> txtJob Then
lngLoop = 1
txtJob = rs!("Job")
End If
rs.Edit
rs!ListLineNum = lngLoop
rs.Update
lngLoop = lngLoop + 1
rs.MoveNext
Loop Until rs.EOF
rs.Close
Set rs = Nothing
Set db = Nothing

Set MyRS = Nothing
Set conn = Nothing

End Function

The table: tblMySource

field Job is text, 8 characters, allow zero length.
5 other fields; number.

 

[RoyVidar]

Hi!

Try using either the syntax:

[tt]If rs("Job") <> txtJobNumber Then[/tt]

witout the bang (!) or

[tt]If rs!Job <> txtJobNumber Then[/tt]

I'm also a great fan of explicit declarations of objects, also specifying the library:

[tt]dim rs as dao.recordset
dim db as dao.database[/tt]

Roy-Vidar

 

[scriverb]

Let's change the following:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = db.OpenRecordset("tblMySource", dbOpenDynaset)

You had not make a "type" declaration in the OpenRecordset statement. Let's see if that is it.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dr1256dr]

It works!

Bob, Roy-Vidar...you were both right. I needed the explicit declarations. But that wasn't all of it. I also needed to lose the ! in the syntax.

Thanks for all the help, I can now lose 5 queries, 5 tables, and a bunch of messy code!

Stars for both. Even if Bob spent the most time ;-)

Dennis

 

[scriverb]

dr1256dr: Glad that we got it working for you. Thanks for the Star. Good luck with your project.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]