Easy Autofill in Microsoft Access

[@underbubbles]

After 4 different microsoft autofill video tutorials, dead ends with visual basic and errors and several wasted days THIS ONE WORKS! Thank you!

[@andrewstechlessons]

Thank you so much! That really helps a lot 😊

[@Mzsmartdestroyer]

I came all the way back to say thank you, i was looking for this type of solution,and u made my day , Thanks allot again

[@andrewstechlessons]

So glad I was able to help!

[@abueleaslinkon263]

Thank you so much for making this video. Wish you all the Best !!!

[@andrewstechlessons]

You're welcome Abu! Glad I could help!

[@ali-pollywears1067]

ok ! done andrew thanks i learnt it full

[@andrewstechlessons]

Glad you found the information useful and thank you!

[@tutsecret499]

Is there a way to fill up a field depending on the previous column. Example: Column Race has Pilipino, Mexican, etc. So I want the next column Country to be filled out automatically with the Country name related to the Race. If I type Mexican on the Race column, then on the Country should have Mexico without me typing anything. I get so tired of dropping down combobox box selection when handling massive data, it hurts the hand/pulse and causes disability. I wish Access had the feature VLookup and/or XLookup to do this process easily. I have to improvise but only when I am importing from Excel where I use the Xlookup to fill out the empty Country column according to the Race content column.

[@andrewstechlessons]

Yes I wish a simple Vlookup can work on Access. What I did on this video is basically the same thing. It'll pull whatever is in another table joined like the lookup. You can also custom what shows up on the drop down as well.

[@GThe-su9kl]

That's interesting, but I can't find the info that I'm looking for. I'm trying to see if it is possible to mass select a bunch of fields to fill them with a singular value - so, for exemple, in this exemple, if you had a table with students, topics and level, and now you wanted to add a "teacher" column where in some cases, there is only one teacher for a given combination of grades and topics, you could probably find this combination (giving you a row of, let's say, 60 to 90 fields) to fill with their common "teacher" value. Could you "select all" this column and "paste in" all the fields the common info that you want?

[@andrewstechlessons]

One interesting idea you could try is make do a combo box and make it a list where you can allow editing. We can meet on zoom or something to figure something that would work best for you. Send me an email and we can work something. So far it looks like we have a lot of folks with some additional questions. I'd love to do a mass group video with discussion. I could schedule one but want to make sure there is a time that works for everyone from the comments from this video. Here's my email "[e-mail]"

[@GThe-su9kl]

@@andrewstechlessons I searched a bit, and I think that maybe trying to run an update query where I ask it to change (NZ) to the data that I want might work. I'll have to test that.

[@GThe-su9kl]

@@andrewstechlessons It worked, but I had to change things a bit. The update query did not want to change "null value" in a column set with choices, so I had to "remove the choice", fill the column and then rehabilitate the choice. Thanks for your help!

[@andrewstechlessons]

@@GThe-su9kl glad you figured it out!! Good job 😀

[@GThe-su9kl]

@@andrewstechlessons thanks! Have a nice day! 😄

[@hazelfelicilda4120]

Sir in my case i have a form and i want it when i add new record the userid of the peron who logged in will automatically filled in in the UserID field

[@andrewstechlessons]

Best way to do that from my experience is to make a separate table & form and join that table to whatever you table/form you want it to automatically fill in the data from. So basically you would make a table for customers to be entered once. Then make another table for purchases that will reference the customer making the purchase joined by the primary key. Feel free to let me know if you'd like me to do a 1 on 1 with you to show you what I'm referring to.

[@paulnyamtowo]

Hi Andrew Bartlett, l have followed the same steps you did in creating an autofill form for your students but the fields which were auto-filled in the query/form are not showing when l open the Student Table. Only the field which l have input manually on the form like the class code and class in your case, are only showing, could you please help me with that

[@andrewstechlessons]

Yes sadly I should've mentioned that at a side note. Because not all the fields were being used in the query it won't show up in the tables. Quick fix is you can go to the query, copy the info then paste/append to the table. Most of the time I usually don't use the table information and just go off the queries and report info. There is a way to do it so it works on all ends with using macro, however I found when doing a large project long ago for some really odd reason it won't work in either certain fields or amount of flelds, I honestly have no idea why it didn't work. My college professor and I couldn't figure it out. This way will always work but you won't be able to get the information to unslected fields in your tables. One day I'll figure out some way it works perfect. Also you don't want to rely on macros because left untouched the formulas stop working. I've had this happen a lot. Sorry for the very long answer. The best way to make this perfect is by limiting the fields in the table to exactly what you need in the queries for auto fill that way it works perfect for everything. Hope this helps and if you'd like you can send me your email in an email so it's not on the web and I'll try to send some videos I sent to people showing the issues so you can see the real life examples of this.

[@satyabanukil779]

Sir I have a tabular form where there is a column called ABScore which has 5 rows each data which means there are 5 records under ABScore Col. Now I want to row number 1 will loop through other 9 records and compare with other rows. If row number 1 carries highest value, then it will put 1 to the column next to it since it secured highest point. Similarly, the second row will compare value with other 4 rows i. e. Rows 1, 3, 4 and 5 and make appropriate ranking against it's corresponding column/rank . But I don't know the way how to do it. I'm from India where scope to learn is very small. Therefore request you to please make a tutorial on updating records by looping. Thnx and regards.

[@andrewstechlessons]

I'll kind of lost, are you trying to loop or repeat the column? Let me know if you'd like to set up a virtual meeting and I'd love to help! Here's me email [e-mail]. send me your info and what time you'd like to meet. I'm on CST just a heads up

[@tutsecret499]

Thank you. How do I store the Age from my query Calculated Age into Age in my table. Despite that I can view the calculated age in the query, but at my work they want to see the Age stored on the table as well. The way I found but manually was to highlight the whole column Calculated Age, and highlight the Age column from the table and paste it, and work. Either I go to the table and paste, or I just do inside the query where I put the Age column from the table. So is there a way a macro or VBA to emulate this copy and paste behind the scene to do this routine. Another way is keep the user using the query instead of the table to make all the inputs, and after is done with all inputs for other records, then work on the Age to do the copy and paste. I see people doing inputs directly on query(I do that myself too in some circumstances) instead of using the forms or the table, although the query is just a copy of the table(mirror).

[@andrewstechlessons]

Sorry for the late reply. Not sure if I read this correctly did you have both the calculated field in the table and the query? Would you possibly be able to send screenshots blurring out sensitive data from the database?

[@tutsecret499]

@@andrewstechlessons There is not calculation on the table, only on the query on the Column named Calculated Age, but the Age column(original column on the table needs to have this calculated age result. I copy the calculated age and paste on the column Age. The reason I don't do the calculation formula on the original Age column in the query is because we do appending archive databases to another one single giant database where the Age field does not have calculation that will cause conflict error when we append to these destination giant database. I will try to make a screenshot, how I blur out?

[@smartqualitysistem7316]

thanks a lot

[@andrewstechlessons]

Glad I could help!

[@Rojin.Marzooki]

Why i can write in my query table. It is uneditable for me

[@andrewstechlessons]

It might be the a number of things. Could you send screenshots? Let me know if you'd like to look at it together send me an email: [e-mail]

[@chocsonbronx7117]

When i run the query i am not getting to add a record, the fields jus show up but i cant do anything on the query help pleasee

[@andrewstechlessons]

The old record that pull over and can't be changed in the query. There are options. Here's an example of what can help. You have Customer table and Product Table. You join the IDs of the tables and use Customer names and related info and join with products that were purchased with the customer ID so you can have a query showing the customer info and all they purchased. Now from what I read you mentioned you can't add more records. For this example if you have new info you need to add this to the separate tables. Example adding a new customer or a product. When you join like I did in the video you don't have control to change things brought over. Let me know if this helps or you'd like to schedule a time to meet.

[@chocsonbronx7117]

Hey thanks i figured out what was causing the old problem, but i have new problem that i believe you can help me with, i have a table with three foreign keys that point back to the same primary key in another table and if i try to do what you have done here in this video, the autofill does not work, when the query is ran i get nothin and can do nothing

[@chocsonbronx7117]

I have not problem discussin this with you over a zoom call that will help lots

[@andrewstechlessons]

We can try it next week have a ton of things going on. Send me an email so I can send you my contact info and we can meet from there! Thanks for watching the videos and I really appreciate the questions!

[@chocsonbronx7117]

@@andrewstechlessons ok sure no problem

[@samanthajohnson8304]

I want to make an Invoice (receipt) form and have ACCESS fill in the information. All you did was make a Query not a form.

[@andrewstechlessons]

You can make a form from a query. Let me know if that helps a bit.

[@trxtr_j]

you aren't explaining what or why. you are just clicking and assuming everyone knows what and why you are doing something.

[@andrewstechlessons]

Sorry if this wasn't explained in large detail. Here's a video where I fully explained what a relationship is with data. For the video we used related information kzbin.info/www/bejne/hGjSm2aJYpqFqbs

[@andrewstechlessons]

If you'd like let me know if I could do a 1-1 with you, I'd love to help!