Get record from a linked dbase from a Form

[jessebelcher]

Ok i will try to explain this the best i can. I have a debase with utility pole info in it. Pole number, gps, city, type... and many more. My guy wants to be able to make a table as needed to show the poles that have been changed out. But we dont want him messing with the master file so i made a blank table with just info he needs to add and linked it to the master with the "pole number" as the linker field. I made a form that lets him input the pole number and new info about the new pole. But i want him to see the linked table below the form that shows the old pole info. So i made a sub form and put it below the form he is filling out. When you open the form what ever pole number he typed last its info is displayed in the sub form below from the linked table. Well thats is what i want but i need it to display the current pole number info after he enters it. So after he type say pole number 0012354 and hits tab or clicks on the next form field i need the sub form to update to show the old pole info based off the pole number. I cant upload the file here it is .99 mb but i can email it to some one. Thanks for any help i can get. This is my first post and I hope to have many more.
Jesse B

[jessebelcher]

After removing out almost all of the info i was able to upload the files in question.

Thanks

Jesse

[OBP]

Jesse, I haven't looked at your database yet, but the code that you want, assuming that MainForm/Subform Link is pole number should go in to the pole number fields After Update event procedure.
The code is
me.subform(SubformName).Requery
where (SubformName) is the name of your subform, the VB Editor Wizard will offer you a list when you type in
me.
Although it should happen automatically if you have the Main/Sub form "Link" set up.

If your Main/Sub forms are not Linked via a field then you will have to set the Subform's Query criteria to the Mainform's pole number field. The vba is the same.

[OBP]

jesse, I have a quick look at your form and a much better way for the user to input the Pole Number is to use a Combo Box based on a Query that gets it's data from the Distribution Table. This means that they do not have to type anything, typing can lead to input errors and frustration for the user when it doesn't find the correct "pole".

[OBP]

I have added the code, query and a Combo to your form to see for yourself.
The query that supplies the combo box is in "Snapshot" mode so that the user cannot change any of the data.

[jessebelcher]

jesse, I have a quick look at your form and a much better way for the user to input the Pole Number is to use a Combo Box based on a Query that gets it's data from the Distribution Table. This means that they do not have to type anything, typing can lead to input errors and frustration for the user when it doesn't find the correct "pole".


Thats a great idea and i have put that in my form.And it works great,

Thanks

[jessebelcher]

Jesse, I haven't looked at your database yet, but the code that you want, assuming that MainForm/Subform Link is pole number should go in to the pole number fields After Update event procedure.
The code is
me.subform(SubformName).Requery
where (SubformName) is the name of your subform, the VB Editor Wizard will offer you a list when you type in
me.
Although it should happen automatically if you have the Main/Sub form "Link" set up.

If your Main/Sub forms are not Linked via a field then you will have to set the Subform's Query criteria to the Mainform's pole number field. The vba is the same.

I cant seem to get this to show the pole info after i type it and hit tab or click new_ht. It shows the pole number i did before but not the current one. I do thank you for all your help. It may be simple to you but this is the first form i have made for access. It works but i would like to tweak it some more before i hand it over.. any thoughts you have would be great. I did notice that the file you uploaded did not have the "pole data" table with it so i just relinked it with the one on my pc. Would that have caused this query function not to work in this subform? Thanks again.