Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Unanswered: Newby ?..New Records

    Ok, I'm having an issue that I need to solve for multiple forms.

    My situation is that I have a command button on Form A, that opens Form B. Form B, is a lot of basic information, where the ID field of the record matches the ID field on Form A.

    All of this works fine, except when I go to input a new record on Form B. My problem is that the hidden ID field, does not have any information entered into it, so it wont update the Form B table.

    Can anybody lead me in the right direction on this, or lead me to a site that might be able to explain this to me?

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Are there records in Form B yet to match the ID of Form A? If there are (even if there aren't i suppose), are you closing form A when you open form b? you must leave it open so form b can read form a (assuming a relationships is set up). you can open form a *hidden* before opening form b, then when you return, close form b and open form a *normal*. (use window mode if using macro).
    w

  3. #3
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    yes, I do have records in both of the tables being used. When opening form B, I am hiding the ID code for the rows information. My problem is that when I select the >* to create a new record, the ID field is left empty, and because of this, a new record cant be saved. I'm sure I'm missing a simple point, just not sure where it is.

  4. #4
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    also form A ais left open when form B is open. It does pull up all of the normal records fine for the form, it just wont allow me to save a new record since the ID field is a requirement.

  5. #5
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    i think i know exactly what you mean...and you can't use SetValue for the ID field either, right? i usually solve this by using a subform. i came up with another way of doing it but it's convoluted and will wait for someone else to respond first. sry.
    w

  6. #6
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Actually setvalue might be the problem since I've never had to use it before. It sounds like it would work (by automatically setting the value of a new record to the information listed on Form A). If this is it, can you provide a link to somewhere good to research how this works, orjust give a general example?

  7. #7
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi guys, may I join in? I'm sorry but I don't have a double 'z' in my login.

    Let me summarize my understanding of the requirement before answering the wrong question, as I have just done on another post. inzzane you have a continuous form (B) that shows all child records for the current ID on form A. On form B you have hidden the control showing the ID. When you navigate to add a new record on form B you cannot complete the operation because the hidden control is empty whereas it should contain the same value as all other rows in the list.

    OK, whether the control on form B is hidden or not, and whether form A is open or closed are red herrings: you need some way of detecting that the control is empty and if so inserting the correct value.

    I'm not even going to try for a convoluted solution using macros as a solution using VBA is simple and straightforward. First though we need to choose an event on form B when the presence of an ID is tested then we need to devise a way of obtaining the ID value and inserting it into the control on form B.

    I suggest the event used is Before Update for form B. You could use On Current but this may lead to adding records containing only an ID.

    Now if you can guarantee that form A is always open when this operation occurs then we can retrieve to ID value from form A. However it is safer to poke the ID value in a safe place and then retrieve it from there. The safest place in this instance is form B itself. Here is one way of passing the value to form B.

    In design mode for form A select the command button that opens form B. In the Properties dialog select the Event tab and the click in the On Click line. Finally click on the elipses at the right-hand end of the line (the three full stops). This takes you to the VBA code window and directly to the code underlying the command button. Somewhere in the code is a line beginning DoCmd.OpenForm

    Following that will be the name of form B some commas and a variable called stLinkCriteria If this is not so then you are opening form B some other way and this write up does not apply.

    There are three more optional arguments that may be passed as part of the OpenForm method, the last of which is OpenArgs that is the one we want to use. So after stLinkCriteria type two commas to delineate the first two arguments and then type something like Me.ID where ID is the name of the control on form A that contains your ID value. Close the VBA window and save the design of form A. We're finished with it.

    Now open form B in design mode. All we have to do is test for the absence of the ID and then insert it. Select form B (to do this click in the small grey square at the top left corner). In the properties dialog select the Event tab and then click in the Before Update line. Again click on the elipses and Access takes you to the VBA code window and positions you in the Before Update event. This will probably be empty except for Private Sub and End Sub lines. Between these lines we need to add the code to test for and insert the ID.

    Now the test for the absence of an ID depends very much on how you have set up the control and database column and whether you have specified any default values. I assume you haven't and suggest some code that takes care of both numeric and text values. So between the two lines type the following.

    If IsNull(Me.ID) then
    Me.ID = Me.OpenArgs
    Exit Sub
    ElseIf Me.Id = "" then
    Me.ID = Me.OpenArgs
    Exit Sub
    End If


    Close the VBA`window, save the design of form B and test your system
    Rod

    fe_rod@hotmail.com

  8. #8
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Rod, thanks for the in-depth help! Thats exactly what I needed to know, as I need to use this on about 5 seperate forms that all work the same way. I've tested it on one, and it works perfect! It also helps repair some issues that I'd created trying to solve it using macros.

    *I'm bookmarking this page! :P

  9. #9
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60


    wazz

    P.S. work on that double-z login will ya!

Posting Permissions

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