Help!!

[aziz rasul]

First of all I'm NOT a VBA person. Hence the difficulty that I'm currently facing.

The problem is this.

I have 2 tables (tblSFF - Nynex, tblLO's) that contains data like so:-


[tblSFF - Nynex]![New LO Reference]

62600000000000000000
62600000000000000000
62600000000000000000
62700000000000000000
62700000000000000000
62800000000000000000
62800000000000000000
62800000000000000000
62800000000000000000
62900000000000000000
etc

[tblLO's]![LO ID] [tblLO's]![Number]
626 47624
627 52698
628 56245
629 58612
etc etc

I would like a function that would produce the following, in [tblSFF - Nynex]![New LO Reference] i.e.

[tblSFF - Nynex]![New LO Reference]

62600000000000047624
62600000000000047625
62600000000000047626
62700000000000052698
62700000000000052699
62800000000000056245
62800000000000056246
62800000000000056247
62800000000000056248
62900000000000058612
etc

Any ideas. I can email the portion of the relevant bit of the db, if that helps.

[This message has been edited by aziz rasul (edited 12-13-2000).]

 

[simongallop]

Where is the data coming from for the two tables. Is it automatic or through forms? The information will then help us to work out what code and where it should sit

 

[kaspi]

Hi Aziz
If I understood well, you just need to generate a new reference from that looks like "62600000000000047624" from two reference "626" and "47624".
As it seems that all your references are fixed strings you could try the following.
Create a query from your "tblLO's" table and create a calculated field eg.
NewReference: [tblLO's]![LO ID] & "000000000000" & [tblLO's]![Number].
When you run the query you should get your new reference. You can update your "tblSFF - Nynex" table based on the link first characters of "new lo reference" and "Lo id".
Hope it helps
Kaspi

 

[aziz rasul]

Some additional info is required in order to understand what I'm trying to achieve. The data that I initially sent was a typical snapshot.

The contents of "tblSFF - Nynex" come from spreadsheets that are generated on a daily basis. Hence I import the data into the table (after clearing the old data).

The contents of "tblLO's" is a fixed table with a list of 234 [LO ID]'s. The [Number] field is generated, at the moment, by adding an AutoNumber value (from a dummy table) to the [tblSFF - Nynex]![New LO Reference] field. The LAST AutoNumber value per [LO ID] is then transferred into [tblLO's]![Number].

At the moment when I run the database, next time, I typically get (assuming the last value in the AutoNumber field was, say, 4356:-

62600000000000004356
62600000000000004357
62600000000000004358
62700000000000004359
62700000000000004360
62700000000000004361
62700000000000004362
62700000000000004363
62700000000000004364
etc

However what I want is, assuming I have the following:

[tblLO's]![LO ID] [tblLO's]![Number]
626 4356
627 5463

62600000000000004356
62600000000000004357
62600000000000004358
62700000000000005463
62700000000000005463
62700000000000005464
62700000000000005465
62700000000000005466
62700000000000005467
etc

In other words I want the AutoNumber sequence to begin (for each [LO ID]) based on the value in [tblLO's]![Number] AND NOT continue from where it left off.

I can solve the problem by creating 234 dummy tables for each [LO ID] and generate an AutoNumber for them. However this seems a complicated way of solving the problem. Hence the need for VBA, I think.

Simon,

I hope that the above answers your question.

Kaspi,

Thanks for your input. However how would I generate the remaining consecutive numbers for each [LO ID]?


[This message has been edited by aziz rasul (edited 12-14-2000).]

 

[simongallop]

I think that I understand what you are trying to do. You are going to need to play around with recordsets. the following code works but crashes out so a problem for you to sort out

Dim sfff As Single
Dim MySearch As String
Dim LastDigits As Variant, StripAmount As Single
Dim NewSff As String, StartSfff As String

Sub NewNumbers()
Set MyDB = CurrentDb
Set RSsff = MyDB.OpenRecordset("sfff", dbOpenDynaset)
Set RSLO = MyDB.OpenRecordset("LO", dbOpenDynaset)
RSsff.MoveFirst
Do While Not RSsff.EOF
StartSfff = RSsff("New LO Ref")
sfff = Left(StartSfff, 3)
MySearch = "[LO ID] = " & sfff
RSLO.FindFirst MySearch
LastDigits = RSLO("LO Number")
StripAmount = Len(LastDigits)
Do
NewSff = Left(RSsff("New LO Ref"), (20 - StripAmount)) & LastDigits
RSsff.Edit
RSsff("New LO Ref") = NewSff
RSsff.Update
RSsff.MoveNext
LastDigits = LastDigits + 1
Loop Until RSsff.EOF = True Or RSsff("New LO Ref") <> StartSfff
RSLO.Edit
RSLO("LO Number") = LastDigits
RSLO.Update
Loop
RSsff.Close
RSLO.Close
MyDB.Close

End Sub

Stick this in a module, make sure that the fields are correct and run. Suggest that you do it in a copy of your db also this code is not as efficient or structured as could be, but it does work (apart from crashing when no records!)

HTH

 

[aziz rasul]

Thanks Simon. I'm not a VBA person. However I will do as you suggested. At least it will help me to learn about recordsets, which I've wanted to do for some time.

 

[aziz rasul]

Simon,

Can I email you the database? I have tried to resolve the problem using your code. However I don't know where or how to call the module.

If you can't, no worries.
[This message has been edited by aziz rasul (edited 12-19-2000).]

[This message has been edited by aziz rasul (edited 12-19-2000).]

 

[simongallop]

On a form create a command button.
The wizard will kick in. Click cancel.
Right click on the button and a menu will appear. At the bottom of the menu is properties. Select it.
Middle tab of the properties window is EVENT.
Within Event, place your cursor in the box next to ON CLICK. To the right of the box will be a dropdown arrow and a box with 3 dots. Click on the 3 dots.
A new window will appear with 3 selections. Choose CODE BUILDER. This will open the form's module sheet and you should see the following:
Private Sub Command0_DblClick(Cancel As Integer)

End Sub

You now need to type some code to call the procedure. All it is, is the name of the procedure so it should look like this

Private Sub Command0_DblClick(Cancel As Integer)

NewNumbers

End Sub

If you have other controls on the form then Command0 might be called something else.

HTH

 

[aziz rasul]

Simon,

Trying out the code, I seem to be getting a problem. I've been able to isolate the problem on the line:-

Loop Until RSsff.EOF = True Or RSsff("New LO Reference") <> StartSfff

When the inner Do Loop reaches the record with the NEW "Startsff", it retains the previous value for 1 more run before going onto the NEW "Startsff". Any ideas?

I know where the problem lies. I have an index in the "tblSFF - Nynex" table which I think was re-arranging the records.

[This message has been edited by aziz rasul (edited 12-21-2000).]

 

[simongallop]

Not surprised that it didn't work as it is the first time that I have attempted to use EOF. Better to use IsNull instead

It crashes on the EOF part of the Loop until statement. So replace the following (can't guarantee that it will work as do not have time to test!):

Loop Until RSsff.EOF = True Or RSsff("New LO Reference") <> StartSfff

with

Loop Until isnull(RSsff("New LO Reference") = True Or RSsff("New LO Reference") <> StartSfff

Should do the trick. The original fell over only when you had reached the end. Hopefully by testing the field for null (=>end) it should stop

You might have to change the last loop to loop until isnull(RSsff(etc...)) = true

 

[aziz rasul]

Solved the problem. However I needed to use the IsError function to do it instead of IsNull.

I still need to insert 0's within the [New LO Reference] field. However I will try and see if I can solve that on my own to give me confidence in writing VBA. If I can't do it I'll repost.

Here's the full amended code

Dim sfff As Single
Dim MySearch As String
Dim LastDigits As Variant
Dim StripAmount As Single
Dim NewSff As String
Dim StartSfff As String
Dim MyDB As Database
Dim RSsff As Recordset
Dim RSLO As Recordset

Sub NewNumbers()

Set MyDB = CurrentDb
Set RSsff = MyDB.OpenRecordset("tblSFF - Nynex", dbOpenDynaset)
Set RSLO = MyDB.OpenRecordset("tblLO Identifier's", dbOpenDynaset)
RSsff.MoveFirst
Do While Not RSsff.EOF
StartSfff = RSsff("LO Identifier")
sfff = Right(StartSfff, 3) '[tblSFF - Nynex]![New LO Reference]
MySearch = "[LO Identifier]=" & sfff '[LO Identifier]=[tblSFF - Nynex]![New LO Reference]
RSLO.FindFirst MySearch
LastDigits = RSLO("Number") + 1
StripAmount = Len(LastDigits)
Do While RSsff("LO Identifier") = StartSfff
NewSff = Left(RSsff("New LO Reference"), (20 - StripAmount)) & LastDigits
RSsff.Edit
RSsff("New LO Reference") = NewSff
RSsff.Update
RSsff.MoveNext
LastDigits = LastDigits + 1
If IsError(RSsff.EOF = True) <> StartSfff Then
Exit Do
End If
Loop
RSLO.Edit
RSLO("Number") = LastDigits - 1
RSLO.Update
Loop
RSsff.Close
RSLO.Close
MyDB.Close

End Sub

I think we could have solved the problem using your original EOF coding. I will be posting a new topic on this to see whether it would have worked. It'll be called "EOF" under the Modules & VBA section.

Thanks for your help. Much appreciated as it's introduced me to recordsets. Useful stuff.

[This message has been edited by aziz rasul (edited 12-21-2000).]