Results 1 to 8 of 8
  1. #1
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85

    Set focus from one record in a form control to a corresponding record in a different form.

    Here comes my second post. My database for C&G Vending has already been sent to you.
    You will see that I have a frmSodaCatalogue, tied to tblSoda. In addition there are
    four "satellite" forms, each with its own separate table, that I use to enter
    inventory data for different parts of the vending operations. However, the
    data in the four satellite tables is added automatically from the master
    tblSoda in frmSodaCatalogue.

    No problems adding new records from tblSoda to the four satellite
    tables--tblSodaTallyNorthM, tblSodaTallySouthM, etc.

    The problem comes when I want to DELETE a record in tblSoda
    and have it reflected in the four satellites. I gave up on trying to
    set relationships for the five tables in Access. For one thing,


    there's more than one operation I need to do with each potential
    deletion, and they may not always be the same. So it seems more
    reasonable to deal with each situation in code.

    The only thing holding that up is that I can't get the focus to
    move from the target record in frmSodaCatalogue to the
    corresponding records in the satellites. I've read about various
    functions or methods that supposedly do this: SQL statement,
    Find, Seek, DLookup, etc. Problem is that it seems that none of
    these will accept a value stored in a variable. Instead the user
    always has to take some action: type a name into a box, click
    on an item in a list box, etc. I don't my users to have to do that.

    Let's say the current ItemName in tblSoda is "Brisk." That field
    is the PrimaryIndex. I store that value in variable ItemNameRec.
    So far so good. But I can't get any of those critters named above
    to accept it as meaning "Brisk." They just ignore it, or crash.

    Which, if any, of those things above seems best for my purposes?
    I would take a guess at Seek, if I just get it to recognize that
    ItemNameRec is to be interpreted as "Brisk."

    P.S. How do I give praise to you guys for your help? Just
    choose one of the icons? Wouldn't words be better?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Query objects cannot read VBA variables but can call a function that returns a value.

    Query objects can read TempVars (i have never used TempVars).

    I have never used Seek.

    Have you tried opening satellite form with filter criteria?

    DoCmd.OpenForm "form name", , , "fieldname='" & ItemNameRec & "'"

    Just choose an icon and make comment if you want.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    If I understand you correctly, the openform choice would actually open a satellite form
    to the corresponding record in the master form. Then the user could choose to delete
    that satellite record with a Delete button. Problem is, that requires additional actions
    on the part of the user.

    I don't actually want to open any of the satellite forms--only do the deletions
    behind the scenes when the user clicks Delete on the master form.

    So could we focus on VBA code that could do this at the table level
    rather than the form level?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, setting cascade delete in relationships should do that but think you already said didn't want to rely on that.

    So VBA could run DELETE action SQL.

    CurrentDb.Exectue "DELETE FROM tablename WHERE fieldname='" & ItemNameRec & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Well, I'll be darned! It worked! Could you explain those very complicated single and double quote strings that seem to make the difference?

    I mentioned earlier that I needed to do some more things before doing the delete. Specifically I need to make sure that any
    matching ItemName records in the satellite tables don't have a tally (count) larger than 0. If I let them delete a
    record while October counting is being done, and counts have been entered for that item, that data would be lost and our
    reports inaccurate.

    At the end of each annual count, after reports, etc. are done, I'll give them a closeout button to
    clear all count data for that year, leaving all items intact in both the master and satellite tables,
    but reset to 0 values in preparation for next year.

    Perhaps you could tell me how to set something in place which would allow
    deletions to be allowed or not. When it's turned off a label could say
    "Deletions not allowed" or whatever. When it's turned on, it would say
    deletions are allowed. I think I see how it could be done with a Boolean item
    that could be to True or False. Then put in a line in the Delete button that if it's
    set to False, display the message and stop further execution. If it's
    set to True, don't display anything--just zap that record. That way
    they wouldn't have to see any warning messages in the pre-season,
    only after counting data has been entered.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The quotes define a string of literal text. The code is concatenating literal text to a variable to form a valid SQL action statement.

    The apostrophes are used to delimit a text value for a text type field. So if the input value is something like "this is my item name" - with spaces and other non-alpha characters, the SQL knows where the text begins and ends in the statement. Date/time field values would need # delimiter, number fields are not delimited.

    What would be criteria to 'turn off' deletion? Can set form AllowDeletions property to No. Then have a custom button on form to initiate a delete. This button can be set visible/invisible depending on some criteria. Or allow record deletion with keyboard Delete and have code in form BeforeDelete event to validate/confirm.

    The more 'user friendly' the more code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85
    Ah, that's why I couldn't get it to accept the actual ("Brisk") text stored in the variable. It seems there
    are both a single quote and double quote in each delimiter set. I realize the first and last double quotes
    define the SQL command as a whole.

    I've figured out how to handle the Enable/Disable situation. It works.

    I have another issue to bring to the forum, but since it's unrelated to this string,
    I think the proper thing to do is start a new string. Right?

    I can't find any icons to click, but if I could I'd click the smiliest face I could find.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, new topic probably should have new string/thread.

    Click the 6-point start at lower left of post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-24-2019, 11:20 AM
  2. Replies: 5
    Last Post: 10-18-2016, 06:00 AM
  3. Replies: 7
    Last Post: 04-24-2015, 11:29 AM
  4. Replies: 3
    Last Post: 01-19-2013, 01:37 PM
  5. Replies: 11
    Last Post: 01-26-2012, 01:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums