locked
Using a command button in a form to create a second linked record RRS feed

  • Question

  • I'm new to Azzess and Windows programming after a lifetime of Unix, C, Perl, etc. and a non-standard database system.

    As an exercise, I'm setting up a membership database which allows for joint membership of two people living at the same address. My solution for this is to use a command button in the members entry form, which would

    (1) Copy the current record field values into variables and save the current record

    (2) Get the MemberID (AutoNumber) field value

    (3) Create a new record, copying the address and other data expected to be the same from the first record, storing the first member's MemberID and MembershipNo into fields JointID and JointMemberNo and setting the MemberType field to S (for Second member).

    (4) Run a form for entry pf the second member's details. (The user can abort here - if so, the second record would be deleted).

    (5) Get the second member's MemberID and MembershipNo and copy them into the first member's JointID and JointMemberNo fields, and resetting the MemberType to J (Joint member - the default value is I - Individual). Also, just for good measure, entering both IDs and MembershipNos into a record in a second table.

    What I am struggling with is the coding for saving the current record and collecting its ID number.

    Stephen Bird

    London

    Wednesday, October 17, 2012 12:10 PM

Answers

  • Hi Stephen,

    Welcome to the MSDN forum.

    >> What I am struggling with is the coding for saving the current record and collecting its ID number. 

    First of all, build a bound form. To save the current record, please try : DoCmd.RunCommand acCmdSaveRecord .

    To collect the ID in current record, supposing the ID field is txtID, we can refer to it as Me.[txtID]. We can save the value of this ID field to a public variable.

    You may refer to the following link as well:

    How to copy some fields in current record into new record?
    http://www.pcreview.co.uk/forums/copy-some-fields-current-record-into-new-record-t1068169.html 

    Can i create a button that saves the current record, opens a form for a new record and keeps the previous records data in the textboxes?
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/1915e10e-9101-4bf0-9e1d-8cc4866d3e75

    Have a nice day.

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Dummy yoyo Tuesday, October 23, 2012 8:26 AM
    • Marked as answer by Dummy yoyo Friday, November 2, 2012 6:02 AM
    Tuesday, October 23, 2012 8:24 AM

All replies

  • Hi,

    1- The Type of table is ? (Access Table ?, Aother Database ?)

    if it is Access table, just creat an AutoID in that table design, and link that table to access form, then will see the autoID, after upade a field.

    2- Other Way :

    creat a form named : AddNewMBf, based to the memeber table, don't set the default date of the datefield in that table.

    open the that new form, and set the default value of each field based on your memberF from where you add new member.

    the syntax of the (DefaultValue)  for these fields in AddNewMBF : 

    forms![memberf].[CustName]

    froms![memberf].[custAdress]

    etc...

    open that AddnewMBf in hidden mode and set it dateFld to today date from the save on the current memberF , so if that form open it will keep value from memberF, and AddNewMBF will Get AutoID, since the datefld is changed.

    set oncurrentevent, to get info from the AddNewMBF , (only if the memberF id = null)., thenyou can also set the " s, J satus )

    I don't Know if you can handle VBA well, but

    To do all this easely in VBA, Your entire  request need several steps, and  lines of codes.

    I have build a database doing that job exactly, and manging member with card level, payment management per memeber, login on meeting, emaling request to each member, amandement and other activity managements...

    Wednesday, October 17, 2012 12:51 PM
  •           Hi,

    1- The Type of table is ? (Access Table ?, Aother Database ?)

    if it is Access table, just creat an AutoID in that table design, and link that table to access form, then will see the autoID, after upade a field.

    Yes, it is an Access table. I already have the AutoID field - that's the MemberID. But is this field actually populated at the stage where you are entering data in the Access form before saving the record? In the (non-SQL) database I was working on previously a record wouldn't exist until the Save button was pressed - at that point the sequence number would be created, and it could be captured by including a "-s" flag in the command line options of the program creating the record (whether it was a screen (form in Access terminology) or a batch statement).

    2- Other Way :
     
    creat a form named : AddNewMBf, based to the memeber table, don't set the default date of the datefield in that table.

    What default date? The only date being pre-set is the Expiry date, which will be the same for both joint members.

    open the that new form, and set the default value of each field based on your memberF from where you add new member.
     
    the syntax of the (DefaultValue)  for these fields in AddNewMBF : 

    forms![memberf].[CustName]
     
    froms![memberf].[custAdress]
     
    etc...
     
    open that AddnewMBf in hidden mode and set it dateFld to today date from the save on the current memberF , so if that form open it will keep value from memberF, and AddNewMBF will Get AutoID, since the datefld is changed.
     
    set oncurrentevent, to get info from the AddNewMBF , (only if the memberF id = null)., thenyou can also set the " s, J satus )

    I don't Know if you can handle VBA well, but

    To do all this easely in VBA, Your entire  request need several steps, and  lines of codes.

    No previous experience with VBA or VB at all! I learnt programming with Algol 68, and moved on th Cobol, C and Perl.

    I have build a database doing that job exactly, and manging member with card level, payment management per memeber, login on meeting, emaling request to each member, amandement and other activity managements...

     
    Wednesday, October 17, 2012 2:03 PM
  • Hi Stephen,

    Welcome to the MSDN forum.

    >> What I am struggling with is the coding for saving the current record and collecting its ID number. 

    First of all, build a bound form. To save the current record, please try : DoCmd.RunCommand acCmdSaveRecord .

    To collect the ID in current record, supposing the ID field is txtID, we can refer to it as Me.[txtID]. We can save the value of this ID field to a public variable.

    You may refer to the following link as well:

    How to copy some fields in current record into new record?
    http://www.pcreview.co.uk/forums/copy-some-fields-current-record-into-new-record-t1068169.html 

    Can i create a button that saves the current record, opens a form for a new record and keeps the previous records data in the textboxes?
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/1915e10e-9101-4bf0-9e1d-8cc4866d3e75

    Have a nice day.

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Dummy yoyo Tuesday, October 23, 2012 8:26 AM
    • Marked as answer by Dummy yoyo Friday, November 2, 2012 6:02 AM
    Tuesday, October 23, 2012 8:24 AM
  • Hi Stephen,

    I temporarily marked the reply as answer and you can unmark it if it provides no help.

    Please feel free to let us know if you need any help.

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Friday, November 2, 2012 6:03 AM