Autofill one column from another

[feinj]

Hi everyone,

I have a situation where, in a query, I need to autofill the
column called "Xferd Vendor No" from the column called "Vendor No", where if the
data in the Vendor No column changes, then the autofill changes to
match.



Please see the two attached files. The one called Autofill Raw
Data.xlsx shows the expected query results. The one called autofill Final
Output.xlsx shows the final results I need when the task is completed.

[June7]

Is this data imported from somewhere like Excel spreadsheet? The VendorNo column is imported with blanks? How is Access supposed to know which records belong with each VendorNo? Cannot rely on Access to maintain the order of imported records same as in spreadsheet.

Should fix the data before import.

However, if the ID field can be relied on to properly sort the records, VBA code can populate the field. Review another thread with same issue https://www.accessforums.net/program...der-44579.html

[feinj]

Hello June7. I tried to apply the solution you provided and I cannot get it to work in my application. Perhaps you can provide more detail on how you applied your solution to the sample db provided by P J Crittenden. Thanks

[June7]

What I did is described in that thread. What exactly is not clear to you? Did you download that db and attempt to replicate the results before adapting to your db?

What have you attempted? Why doesn't it work - error message, wrong results, nothing happens? Post your code or db with the code.

[feinj]

June7.

As you can see, I am a novice.

I have no idea how and where to apply the code you provided. I did download the example DB and tried to utilize the code. But I do not know how to apply it to get the desired output.

[June7]

Need to understand VBA programming. Start with

http://office.microsoft.com/en-us/ac...010341717.aspx

http://www.access-vba.net/introducti...ccess-vba.html

Do you know how to build a form and put a command button on it? Do you know what the Properties Sheet is? If all yes, then follow the instructions in post 11 of the other thread for putting code behind the command button.

What does 'tried to utilize' mean? What did you do?

[feinj]

I have created a form in the attached example db, added a button, and copied your code to the on-click event procedure. I get a compile error.

Please review the attached DB and get back to me.

[June7]

The Option Compare Database and Option Explicit lines belong in the module header, not within the procedure (see the Option Compare Database line that is in the header by default). Remove the line of underscores.

Remove the duplicate End Sub line. Remove the Sub PopulateHeader() line.

Add the Header field to table.

Run code.

[feinj]

OK June7. Thanks for all the help. It worked after making your suggested changes. Now all I have to do is apply this same logic to my own application.

[feinj]

Hi June7,

I created a new attached DB in which I copied over the code we worked on yesterday and tried to run the code.

It will not work in this new application. Can you please look at it and let me know what needs to change in order for the code to work in the form button.

Thanks,

[June7]

Apparently the Header field is not Null, there is something in there even though it is not visible, so no records are retrieved by the recordset. I deleted the field then recreated. Code then works.

I always set text fields in table to not allow empty strings so when the field looks empty, I know it really is. An empty string is not the same as Null.

Review link at bottom of my post for debugging guidelines.

[feinj]

Hi June7,

I did just what you did and it worked. Thanks again for your help. I will eliminate the Not Null condition for future use.