Subform REquery not working

[Kryst51]

Hi All,

I have looked at and tried several suggestions on this site, and a couple of others.

I have a combo box on a form, that I want to requery a subform based on what is in the combo box. It works, except with one kink. It doesn't requery correctly. As I am not exactly sure if the query behind the subform is correct, I was hoping to get some insight as to what I am doing wrong. You'll notice that When "Comcast" is selected its transactions appear, but when "Ebay" is selected there are no transactions. The Transactions associated with "Ebay" Actually show up when the "Electric Company" is selected, which is not correct.

I have attached the DB.

 

[Kryst51]

Hi All, Nvermind, I took a different approach after some more thought and got it working the way I want!

 

[Kryst51]

Stupid, my new idea is better, but now I can't get the list box on this subform to requery.

Again, I have looked at several threads, and tried many different thing. I actually got it working once, but messed it up and was not able to duplicate my results.

Please find attached.

 

[wazz]

there are several issues, including, setting up relationships.
have a look at the new form and queries as well as the relationships window. it still needs work but hopefully it will get you going.

keep the names of fields the same from one table to the next.

 

[Kryst51]

there are several issues, including, setting up relationships.
have a look at the new form and queries as well as the relationships window.

I have been trying not to do table lookups (Which I have recently learned are not good), which is how I am used to setting up relationships. Did you just do those in the relationship window itself?

keep the names of fields the same from one table to the next.

Part of the reason for having the different names, is I thought it would be better to only have one table for companies and names. Then I could use the entries in that table to populate my "Paid To" and "Paid By" fields. I do this in a different DB with employees, an employee can be an inside salesman (Which he/she is referenced as in one table) or the initiator of an NCR (which he/she is referenced in a different table) either way, there is still only one record in the employee table for that person.

 

[Kryst51]

but hopefully it will get you going.

Thank you so much for your help! The not using lookup fields in tables has confused me beyond belief. I have been using databases for over 2 years now, (Mostly teaching myself), so I haven't learned any other way. Apparently I need to revisit my books on DB design. Again Thank you!

 

[wazz]

I have been trying not to do table lookups (Which I have recently learned are not good), which is how I am used to setting up relationships. Did you just do those in the relationship window itself?

that's good. and lookups are not the same as actual relationships. yes, i set the relationships in the relationship window itself; it's the only way. creating links/joins in queries is also not the same; use the relationship window.

Part of the reason for having the different names, is I thought it would be better to only have one table for companies and names. Then I could use the entries in that table to populate my "Paid To" and "Paid By" fields. I do this in a different DB with employees, an employee can be an inside salesman (Which he/she is referenced as in one table) or the initiator of an NCR (which he/she is referenced in a different table) either way, there is still only one record in the employee table for that person.

oh, i see. that works. i didn't think they would link to the same thing. you could adjust the names slightly to something like, PaidToCompanyID and PaidByCompanyID. then you would easily see that they are related to the same table.

 

[Kryst51]

Hi wazz,

I looked at the form you put on there. It looks great, but what I was trying to gain from my form was all of the transactions available for an entity with their corresponding amounts in a list box. Hence the continous subform with the combo box for entity on the main form.

Maybe now that my relationships are defined I can try my way again!. Thanks again for your help.

 

[Kryst51]

oh, i see. that works. i didn't think they would link to the same thing. you could adjust the names slightly to something like, PaidToCompanyID and PaidByCompanyID. then you would easily see that they are related to the same table.

Fantastic idea! I'll have to do that.

 

[wazz]

you could change the second combobox to a listbox and put it to the left of the existing listbox. not much different, but you would easily see a list of all (most) of the transactions right away. then you could click on the transactions list and update the next listbox.

i'm pretty sure (didn't study too closely) that the way the tables are set up now require a 3-step process.

 

[Kryst51]

you could change the second combobox to a listbox and put it to the left of the existing listbox. not much different, but you would easily see a list of all (most) of the transactions right away. then you could click on the transactions list and update the next listbox.

I appreciate the suggestion. Unfortunately it doesn't meet the purpose for the lookup form. I know there has to be a way to requery that list box when that transaction ID changes on my subform, I just don't know how to do it. I have tried making the listbox from a query and directly from the table. I have put an update event on the "TransactionID" field on the subform telling it to requery the list box. I have tried the same thing on the on change event. I have tried some of the suggestions that Bob Larson has put in different threads, including something I found in a thread that would be place on the subforms on current event. But all to no avail.

Probably a lot of the problem was due to lack of relationships. But now that they are there, Much thanks to Wazz as I would not have realized they were missing, I feel like my logic, what I am trying to attain should be possible.

Edit:

some syntax I have tried in various events are

lstAmounts.requery
Docmd.requery "lstAmounts"

I tried both of these on the "after update" and "change" events on my Transaction ID field

 

[Scooterbug]

I played around with the db a bit. I actually do a lot of searching and such on the db here at work (Find a Customer, click on it to populate a list box of orders...click on an order to see payment info in another list box, etc)

On your form, once you select a PayOut company from your combo, it will populate the list box. Double click on the line item in the list box and transaction details will show up in the subform.

I also noticed how you had your requery setup in the code. When you have a query for a row source for a combo box/list box, I use the following:

me.ComboBox.requery

I dont know if docmd.requey is an issue with what you were trying to do or not...

 

[wazz]

see if the attached helps.

 

[Kryst51]

After putting more thought into it, I think both of your suggestions are useable. I don't think I can achieve things the way I wanted to, but I guess you have to be flexible sometimes. Looking at your examples has helped me tremendously. Thank you for your efforts and advice.