New Record code

[Kundan]

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?

[OBP]

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.

[Kundan]

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.

[OBP]

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.

[Kundan]

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.

[OBP]

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.

[Kundan]

Thanks A Lot!! GOD BLESS YOU!!!!