Find latest record to fill data 1

[cdcampbell]

Hi,
I am not sure if this is possible - I am very new to coding. I think it should be - what i want to do is create an append query where if a specified field is null - i would like it to go back in that table, find the most recent and use that data. When a new one is entered it would stop the 'autofill' and start all over.
Does this make sense?
Any advice would be hugely appreciated

Christy

 

[RickSpr]

You can do this by writing a function such as the following, in a standard code module.

    Public Function LastNonNull(var As Variant) As Variant
        Static last As Variant
        If IsNull(var) Then
            LastNonNull = last
        Else
            last = var
            LastNonNull = var
        End If
    End

What this does is test the passed argument value. If it is not null, it is saved in a local variable (last) and also returned as the function result. If it is null, then the local variable is returned as the function result. The local variable is declared as Static so that its value will be retained from one call to the next.

After you create the function, you use it in your append query for the field you need to propagate. Say the field name is Dept. In the query, replace the "Dept" in the field name row with the expression "LastDept: LastNonNull([Dept])".

A warning is in order. Because the function's local variable is declared Static, its value will persist even after the query has finished. What this means is that, if you run the query a second time, and the first one or more rows being appended contain Null in the Dept field, they will be inserted with the value left over from the previous time the query was run. Or, if the query has not previously been run (since the database was opened, or the code was compiled, or VBA was reset), it will try to insert Empty as the value, which will probably cause an error. Therefore, this technique should be used only when you know the first record to be appended will have a non-Null value in the field. Rick Sprague

 

[cdcampbell]

Oh my goodness
I cant begin to say thank you enough! Really!
I am about to try it and no matter what this is more than i could have come up with on my own - so not a VBA girl yet.
Thank you thank you.
If I could i'd send you cookies!

Christy

 

[cdcampbell]

Rick!
Sorry to bug you - but i need just a bit more help..
I did as you said - and i put it into my query in the field row - what am i doing wrong - do i still choose the table that it is looking for data in? i get an error if I do that the syntax is wrong. when if i do not choose the table all it does is put this: LastAP00: LastNonNull([Dept])
in that field as text. how do i tell it to call the function?
Sorry - i know this is probably a dumb question - someday i'll be a VB/ACCESS Smartie pants - but not today

 

[cdcampbell]

Hoping someone can help me out! Ricks code is great I think i am screwing up with the query part - when i follow these instructions -
.------After you create the function, you use it in your append query for the field you need to propagate. Say the field name is Dept. In the query, replace the "Dept" in the field name row with the expression "LastDept: LastNonNull([Dept])".---
i get the text "LastDept: LastNonNull([Dept])" appear in that field
WHAT am i doing wrong??
Any help would be greatly appreciated

 

[allanon2]

I can help you. I think that you may have to change your append query just a little. Here is what I did and you can see if it is somewhat like your situation.

1. I copied the function to a module. I had to make a slight change so my function looks like this:

Public Function LastNonNull(var As Variant) As Variant
Static last As Variant

    If IsNull(var) Then
        LastNonNull = last
    Else
        last = var
        LastNonNull = var
    End If
    
End Function

The big change here is the last statement. Should be 'End Function' not 'End'

Just a small oversite for Rick

2. I created an update query like so:
UPDATE CopyToTable INNER JOIN tblMembers ON CopyToTable.UserID = tblMembers.UserID
SET CopyToTable.UserInfo = tblMembers.UserInfo;

This worked fine.

3. I deleted a couple of UserInfo data from tblMembers (so I would have some null ones)

4. I changed the query to be:
UPDATE CopyToTable INNER JOIN tblMembers ON CopyToTable.UserID = tblMembers.UserID
SET CopyToTable.UserInfo = LastNonNull(tblMembers.UserInfo);

This is working great. Thanks Rick.


If you read this and still cannot figure it out then post you function code and you query code and I will test you code. If you need help with that just let me know.

 

[cdcampbell]

Thank you Allanon2 -
Sorry to be a pain - I am not quite sure how I could make the update query work. I dont have any consistent fields to join on. I have this big mess of a table that is imported from another application. so i will get the first record containing name information but then some are blank etc. I cannot really use NameID because it is imported as the name text only so it has no way to link to an Employee Table (for example). So in order to make it work as a more proper database, i need to break that into multiple tables. I thought i would do that with queries. Here is one of the queries as is - i just need to change AP00 to use the LastNonNull function.
INSERT INTO tbl_AirBird ( Record_Created, AP00, AD00, AA00, AR97, AR98, AC00, AC01 )
SELECT Whole_Data.Record_Created, Whole_Data.AP00, Whole_Data.AD00, Whole_Data.AA00, Whole_Data.AR97, Whole_Data.AR98, Whole_Data.AC00, Whole_Data.AC01
FROM Whole_Data
WHERE (((Whole_Data.AR97) Is Not Null)) OR (((Whole_Data.AR98) Is Not Null));

Hope I made some sort of sense - thanks again for your time and patience!

 

[allanon2]

Ok. I copied you query and created a couple of tables to match your field names. Ran you query. It worked just fine. The I changed it to be this:

INSERT INTO tbl_AirBird ( Record_Created, AP00, AD00, AA00, AR97, AR98, AC00, AC01 )
SELECT Whole_Data.Record_Created, LastNonNull(Whole_Data.AP00), Whole_Data.AD00, Whole_Data.AA00, Whole_Data.AR97, Whole_Data.AR98, Whole_Data.AC00, Whole_Data.AC01
FROM Whole_Data
WHERE (((Whole_Data.AR97) Is Not Null)) OR (((Whole_Data.AR98) Is Not Null));

This is working good as well.

If you are still having problems then maybe you can email me your database. Actually, I would really like you to email it to me anyhow just so I can see what it is you are trying to do and maybe make a couple of suggestions.

If you would like me to look at it you can email it and an explaination as to what you are trying to accomplish to me at

jwdconsulting@shaw.ca

 

[cdcampbell]

I have sent it - thanks so much for the help.