Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    28

    Talking Unanswered: Small Problem, Don't make me beg :)

    Working in Access 2k When I update, via query, fields that are related to other tables, the form does not refresh the RELATED info on the FORM.

    i.e. Inventory form, using docmd.runsql I update [Linker].[T_Color] to 'BL' and the T_Color_Full should read "BLUE"

    [Linker].[T_Color] is related to [Colors].[T_Color] on T_Color

    The relations and everything work fine, if I do Me.Requery it shows the right info.

    If I do Me.Refresh it shows the new data on the [T_Color] field but not the [T_Color_Full] field, that one still has the old data.

    If I simply change the T_Color manually on the form and move focus to another field it updates the related info... so how do I make it update with out doing me.requery ?

    Thanks tons in advance for your help !

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Becuase of how you are updating the data, I don't believe that you can do it with out requery. Is there a reason why you don't want to use it?

    S-

  3. #3
    Join Date
    Dec 2003
    Posts
    28
    Me.Requery takes too long :-\ Way too long.

    The reason I am changing the data using an update query is because I have to update fields specified in fields, so I have to build strings... query strings.

    like...

    StartStr = "UPDATE Linker SET Linker."
    VarBase = "([Linker].[Inv_number], "
    EndStr = " WHERE (((Linker.Inv_Number) Like '" & InvNum & "'));"

    PV_1_Field = 'T_Color'
    PV_1_Start = 'Right'
    PV_1_Len = '2'

    QryStr = "" & PV_1_Field & "=" & PV_1_Start & VarBase & PV_1_Len & ")"

    DoCmd.RunSQL "" & StartStr & QryStr & EndStr

    So the final Query is something like:
    "UPDATE Linker SET Linker.T_Color = Right([Linker].[Inv_number], 2) WHERE (((Linker.Inv_Number) Like 'BB09SMBL'));"

    So that would set the T_Color to BL and thereby relate BLUE

    If you know a way to get this done with the same flexibility using objects or something PLEASE let me know and Thanks BTW

  4. #4
    Join Date
    Dec 2003
    Posts
    28

    Unhappy

    Please??

  5. #5
    Join Date
    Dec 2003
    Posts
    28

    Exclamation

    Ok... now I'm begging...

    Do my problems just scare people off?? Is my coding so wack as to make people not want to give advice or what?

    I could really use some help...

  6. #6
    Join Date
    Dec 2003
    Posts
    28

    Angry

    Bump!

  7. #7
    Join Date
    Nov 2003
    Posts
    267
    Sorry didn't get back to you sooner.


    If you forms are bound, and it sounds like they are,

    Write the changes back to the textboxes on your form, thereby automating the manual entry into you text boxes, there by updating both the display of your data and the actual record.

    S-

  8. #8
    Join Date
    Dec 2003
    Posts
    28
    They are bound.

    By 'writing the changes to the text boxes' do you mean like forms!fields(PV_1_Field) = 'BL' ? or what Exactly?

    Also, I don't want all of these fields on the form. I put a couple there to trouble shoot, but the user should only see the final result. Not all of the silly little codes.

    THEN there is the issue that the way I build the values for the fields, 'BL' in this case, is by concat'ing other fields, PV_1_Start = 'Right' and PV_1_Len = '1' and I need the value of Right(Inventory_Number, 1) to put in the field specified in PV_1_Field...

    So how to do that WITH OUT queries?? If I knew I would be doing it. That is the reason I went to update queries in the first place. It is the only way I could think of to give this level of flexability. You see, for one product type, t-shirts lets say, the inventory number will be split up differently than say, a lamp.... So I specify by product type how the rest of the inventory number is split up to the different fields...

    Also, you have to keep in mind, you can't mix the methods. Well not without requery between them or something.

    Oh, and S I REALLY appreciate you giving this your time and thought. I know how much there is to learn in this type of stuff and it boggles my mind. I'm sure someone that knows more than me is probably busier than me too! Thanks!

  9. #9
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Small Problem, Don't make me beg :)

    Originally posted by PainGypsy
    Working in Access 2k When I update, via query, fields that are related to other tables, the form does not refresh the RELATED info on the FORM.

    i.e. Inventory form, using docmd.runsql I update [Linker].[T_Color] to 'BL' and the T_Color_Full should read "BLUE"

    [Linker].[T_Color] is related to [Colors].[T_Color] on T_Color

    The relations and everything work fine, if I do Me.Requery it shows the right info.

    If I do Me.Refresh it shows the new data on the [T_Color] field but not the [T_Color_Full] field, that one still has the old data.

    If I simply change the T_Color manually on the form and move focus to another field it updates the related info... so how do I make it update with out doing me.requery ?

    Thanks tons in advance for your help !
    I realize that you are already receiving some help but I noticed you were on line at this late hour and thought I'd share. Have you tried moving off of the current record and then back again or does this cause the same pain as requerying? I mean with code possibly using DoCmd.GoToRecord.

    I know that there is a simple way to do this. There has to be if you can change the field manually and the T_Color_Full field changes.

    I think the solution has to do with what Access does behind the scenes when you move off of one control and commit the changes to a field. You just need to recreate that in code. Someone here has to know this.

    How about just moving the focus to the control whose value you are changing and then moving to the next control in the tab order?

    Am I stretching it?

    Just some late night sleepy thoughts.

    Sorry to barge in but it intrigued me because I thought I knew the answer right off but realized that I didn't.

    Nighty Night.

    Gregg

  10. #10
    Join Date
    Dec 2003
    Posts
    28
    I GREATLY appreciate you "barging in" The more heads on any one problem the better in my book! For instance, that next record idea is a GEM! I never thought of such a simple thing and it might just work. It would be WAY faster than a requery, that is for sure.

    I am leaning away from queries though. I think it is tidier and faster if I do it with objects, which I am not very familiar with. But as you might see in my other post, there are some reasons I am using the update queries. I'm not sure if I use objects that I won't have this same problem with that, but I will try your next record suggestion in either case.

    Thanks $#!+ Loads, Mek

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •