Query / loop: Auto filling a column until next instance..

[biggiewilly]

Hell All,

Is there a way to auto / replace null fields in a column using a LOOP / AUTO FILL UNTIL NOT NULL?? function in a query or something else.........?

ISSUE: 3 columns
TABLE TERM COUNT
CAH
LM 5
SD 134
TH 8
TP 23
WN 24
CAM
SD 52
TH 4
TP 12
WN 3
SUMMARY
LM 5
SD 186
TH 12
TP 35
WN 27

Im trying to get the table column to fill in the null rows of the table column with the 1st instance of a text (CAH ect.) until it comes to the next text then start to fill in those null fields and so on.

So the end result would like this:

TABLE TERM COUNT

CAH
CAH LM 5
CAH SD 134
CAH TH 8
CAH TP 23
CAH WN 24
CAM
CAM SD 52
CAM TH 4
CAM TP 12
CAM WN 3
SUMMARY
SUMMARY LM 5
SUMMARY SD 186
SUMMARY TH 12
SUMMARY TP 35
SUMMARY WN 27

Any help would be greatly appriecated

 

[NthDegree]

A little bit of code (and if you dont have any forms with VBA code attached where you could run this code from) a simple macro.

create a module and add the following code:

Option Compare Database
Option Explicit

Public Function Update_Column()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strTempHeader As String

    strSQL = "Table1"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    With rs
        Do Until .EOF
            If !Table = "" Or IsNull(!Table) Then
                .Edit
                !Table = strTempHeader
                .Update
            Else
                strTempHeader = !Table
            End If
            .MoveNext
        Loop
    .Close
    End With
End Function

Then just create a macro with action column set to [highlight]RunCode[/highlight] and the function name field set to [highlight]Update_Column[/highlight]. Save macro then run it.

 

[biggiewilly]

Thanks NthDegree

Unfotuntly the code will not work. I understand the loop but theres nothing in my declarations as "database"

Getting a complier error:

User-defined type not defined.

"db As Database" becomes highlighted and
Public Function Update_Column()turnes yellow??

??

The database name is "AGENT"
Table name is "TEST"
Coloumn name is "CALL TABLE:


Thanks....

 

[dhookom]

I haven't looked at the code but you would need to have a Reference set to the MS DAO object library and then possibly change your code to:

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

One thing to keep in mind is that Access records have no real order unless you apply a sort order. Think of Access/Jet records as school children on a playground. There is no order until you blow the whistle and have them line up in a particular order.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NthDegree]

dhookom is correct, when you enter the code, you will need to click on tools, references and then add the reference "Microsoft DAO 3.6 Object Library" that will have the definition for the database and recordset keywords so the interpretor knows how to process them. Depending on your version of MS-Access you're version of the DAO object library may be different.

 

[NthDegree]

You will not need to add the DAO. though.

 

[dhookom]

Adding the DAO is "best practice". If ADO is listed above DAO in your References, you may get an error if you omit the DAO from the Dims.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[biggiewilly]

Works great,,, Thanks Guys..


Option Compare Database
Option Explicit

Public Function Update_Column()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strTempHeader As String

strSQL = "COMBINED CONNECTS"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
Do Until .EOF
If !CALL_TABLE = "" Or IsNull(!CALL_TABLE) Then
.Edit
!CALL_TABLE = strTempHeader
.Update
Else
strTempHeader = !CALL_TABLE
End If
.MoveNext
Loop
.Close
End With
End Function

 

[Craig0201]

Buy Monarch?

 

[dhookom]

Buy Monarch? What fun would that be?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NthDegree]

Buy Monarch? For that little snippet of code? The proverbial "killing a fly with a shotgun" theme.

 

[dhookom]

Don't get me wrong, I would rather buy a tool if it cuts the need for resources in the long run. If biggiewilly had this type of stuff to do over and over with different formats etc, the purchase might be justified.

At least this is my reasoning to my wife when I bring home another tool from the hardware store ;-).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NthDegree]

Amen, Brother.

 

[biggiewilly]

True but I need the practice and to learn how to write this not just copy it from someone else "not with speaking about this one" ........