Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

Loop and addd alphebetical sequence

I need to loop through a recordset and add records with sequential letters... specifically A - I.
Below is the code I have so far, which obviously is not working.

Dim j As Text, i As Text
Dim rs As DAO.Recordset
i = "I"
Set rs = CurrentDb.OpenRecordset("Publication_Ad")
For j = "A" To i
       With rs
              .AddNew
              !Ad_ID = "36.02"
              !Position = j
              .MoveNext
              End With
              Next              
rs.Close

What am I missing....?

~Worcse
0
Worcse
Asked:
Worcse
  • 4
  • 3
  • 3
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
One thing you are missing is Update:


Dim j As Text, i As Text
Dim rs As DAO.Recordset
i = "I"
Set rs = CurrentDb.OpenRecordset("Publication_Ad")
For j = "A" To i
       With rs
              .AddNew
              !Ad_ID = "36.02"
              !Position = j
              .Update
              .MoveNext
              End With
              Next              
rs.Close
0
 
WorcseOwnerAuthor Commented:
MX - thank you for the response.  I added the .update and am still getting the
Compile Error: User defined type not defined... specifically trying to define J as text.

I know that I can add a numerical field to my table, set j and i as interger, loop through and add nine records with 1 - 9 respectively and then update the corresponding text field... but I was hoping to be able to streamline the process and just loop and add the sequential text records...

~Worcse
0
 
als315Commented:
If you are adding records(AddNew), you don't need MoveNext.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
IrogSintaCommented:
How about this way:

    Dim rs As DAO.Recordset
    Dim c As Variant
    
    Set rs = CurrentDb.OpenRecordset("Publication_Ad")
    With rs
        For Each c In Split(StrConv("ABCDEFGHI", vbUnicode), vbNullChar)
            .AddNew
            !Ad_ID = "36.02"
            !Position = c
            .Update
        Next
    End With
    rs.Close
    Set rs = Nothing

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Yeah ... there are actually multiple problems ...
0
 
als315Commented:
OR
Dim j As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Publication_Ad")
For j = Asc("A") To Asc("I")
       With rs
              .AddNew
              !Ad_ID = "36.02"
              !Position = Chr(j)
              .Update
       End With
Next j
rs.Close

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Here is a take off on IrogSinta's code  (no points)

    Dim c As Variant
    With CurrentDb.OpenRecordset("Publication_Ad")
        For Each c In Split(StrConv("ABCDEFGHI", vbUnicode), vbNullChar)
            .AddNew
            !Ad_ID = "36.02"
            !Position = c
            .Update
        Next
    End With
0
 
WorcseOwnerAuthor Commented:
als315 - well done...!
Worked like a charm.  No errors and updated the table accordingly.
For my own edification... what does Asc() do?

~Worcse

Thank you to everyone else to participated at this late hour
0
 
IrogSintaCommented:
@MX,
That's interesting, I didn't know you could shorten it that way.

@Alex
Using Asc() is definitely better than just stating 65 to 73 which I normally would have done.

I'm glad I participated, I'm always learning something new!


/Ron
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
@IrogSinta

The fastest way is:

    Dim c As Variant
    With DBEngine(0)(0).OpenRecordset("Publication_Ad")
        For Each c In Split(StrConv("ABCDEFGHI", vbUnicode), vbNullChar)
            .AddNew
            !Ad_ID = "36.02"
            !Position = c
            .Update
        Next
    End With
0
 
IrogSintaCommented:
For my own edification... what does Asc() do?
@Worcse
The ASC function returns the ASCII code for a character.  For example, the ASCII equivalent for the character A is 65 in decimal.  You can see a list of codes in this ASCII table.  The CHR function is the reverse.  CHR(65) returns the character A.
0
 
WorcseOwnerAuthor Commented:
NICE!!.... thank you for the info.
Looks like you are well on your way to 4 stripes...!!!
Best of luck.
~Worcse
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now