Consulting

Results 1 to 7 of 7

Thread: New Record code

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Posts
    41
    Location

    New Record code

    Private Sub MoveToMain()
    Me.Parent.FNAME = NAME
    Me.Parent.LNAME = LNAME
    Me.Parent.ADD = ADD
    Me.Parent.[ADD 1] = [ADD 1]
    Me.Parent.[ADD 2] = [ADD 2]
    Me.Parent.PLACE = PLACE
    Me.Parent.PIN = PIN
    Me.Parent.DISTRICT = DISTRICT
    Me.Parent.STATE = STATE
    End Sub

    I have written the above code to transfer fields from subform to main form. This transfer takes place when I click a field on the subform. But I want to ensure that the transfer is to a new record on the main form. It should not overwrite old records on the main form. How to achieve it?

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I understand what you are asking, what I don't understand is why you wish to do so.
    The main form and subform are normally based on different tables, in which case you are duplicating data.
    Also the mainform & subform should be related by Master/Child Links, if the main form record has not been created then the Key field also hasn't been created either which could lead to problems.
    You do not say what the version of Access is that you are using or how the data in the tables is laid out and related.
    I think to do what you want you would first have to set focus on the mainform rather than using the moveto command and then move to a new record.

  3. #3
    VBAX Regular
    Joined
    Nov 2018
    Posts
    41
    Location
    Quote Originally Posted by OBP View Post
    I understand what you are asking, what I don't understand is why you wish to do so.
    The main form and subform are normally based on different tables, in which case you are duplicating data.
    Also the mainform & subform should be related by Master/Child Links, if the main form record has not been created then the Key field also hasn't been created either which could lead to problems.
    You do not say what the version of Access is that you are using or how the data in the tables is laid out and related.
    I think to do what you want you would first have to set focus on the mainform rather than using the moveto command and then move to a new record.



    My main & subform are based on the same table. I am not duplicating data. My subform contains addresses already present. If a new record on the main form requires the same address then I need not type it, I will just click on the subform.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So how do you know which address to use in the Subform for the Mainform Record, the point is if you create a new record with VBA code there will be no Person entry link to an address.
    The sub form should not be visible unless there is an entry in the person fields, but then how do you know which address to use and how do you select it?
    Or are the addresses the key field?

    ps unless the Address is in a seperate table to the person then you are duplicating the data ie you have the same address in the database more than once.
    Last edited by OBP; 01-05-2019 at 03:15 AM.

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Posts
    41
    Location
    Quote Originally Posted by OBP View Post
    So how do you know which address to use in the Subform for the Mainform Record, the point is if you create a new record with VBA code there will be no Person entry link to an address.
    The sub form should not be visible unless there is an entry in the person fields, but then how do you know which address to use and how do you select it?
    Or are the addresses the key field?

    ps unless the Address is in a seperate table to the person then you are duplicating the data ie you have the same address in the database more than once.
    I typed the following command:
    DoCmd.GoToRecord acDataForm, Me.Parent, acNewRec


    I am getting the following error:

    Runtime error ‘2498’:
    An expression you entered is the wrong data type for one of the arguments.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It does not recognise Me.Parent, it has the be the actual name of the Form.
    I still say you shouldn't be duplicating the Address, but if you have to then you should do the duplication from the Main form.
    ie select the person with the address and then create a new record from there and transfer the data over.
    This can be achieved by assigning the data to string variables before opening the record.
    Or you could use an Address selectin Combo.

  7. #7
    VBAX Regular
    Joined
    Nov 2018
    Posts
    41
    Location
    Thanks A Lot!! GOD BLESS YOU!!!!

Tags for this Thread

Posting Permissions

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