Copying values from one form to another, when the second form is in data entry mode (1 Viewer)

[gaston]

Hi Guys and Girls

I have a main form (frmCustomerDetails) which is used to enter details on our customers, a subform in this form (frmCustomerContactDetailsSubform), which has some fields locked (e.g. Full Name, which is a concatenated field) and consequently cannot be used for adding new contacts. Lastly I have a form for adding new customers (frmAddNewContact). This form is set to data entry, and is not for editing existing contact records.

Forms as below

frmCustomerDetails
CustomerID (pk)
CustomerName
CustomerAccountNumber

frmCustomerContactDetailsSubform
CustomerID
Salutation
ContactFullName (which is concatenated in the query behind the form)
ContactPosition
ContactDept

frmAddNewContact
CustomerID
Salutation
FirstName
SecondName
ContactPosition
ContactDept

On my frmCustomerDetails form I have a button that launches frmAddNewContact. What I also need this to do is copy 'CustomerID' from frmCustomerDetails, and paste it into 'CustomerID' the new record in 'frmAddNewContact'.

Please note again that this is not being used to find records in frmAddNewContacts with the same 'CustomerID' as the record in frmCustomerDetails, but is solely for adding new records. This form is for data entry only.

When 'frmAddNewContact' is closed I will presumably have to get a routine to requery the 'qryCustomerContacts', upon which the frmCustomerContactDetailsSubform is based.

All help appreciated.

Thanks!

 

[wazz]

you can send the id when you open the 'add' form:
docmd.openform "frmAdd",,,,,,Me.ID

then in the 'add' form's Open event you could say:

[COLOR=green]'set the id when the form opens[/COLOR]
If Me.OpenArgs <> "" Then
Me.txtID = Me.OpenArgs
End If

or you can not send the id, but when the form closes do:

[COLOR=green]'set the id when the form closes[/COLOR]
[COLOR=green]'check if the id is (null or) a zero length string[/COLOR]
If Me.txtID & "" = "" Then
Me.txtID = Forms!frmCustomerDetails!txtIDcontrol
End If
[COLOR=green]'requery the calling form[/COLOR]
Forms!frmCustomerDetails.Requery

there is another way if you open the 'add' form in dialog mode. i'll leave it like this for now.

 

[vbaInet]

If Me.txtID & "" = "" Then

wazz, I've noticed you always do the above when checking for null. Is it a way of iniatilising to ensure that the comparison works? Would CStr(Me.txtID) perform the same or not? Thanks

 

[gaston]

you can send the id when you open the 'add' form:
docmd.openform "frmAdd",,,,,,Me.ID.

Sorry Wazz, not to sure what you mean here. What would I do with the docmd.openform stuff

then in the 'add' form's Open event you could say:

[COLOR=green]'set the id when the form opens[/COLOR]
If Me.OpenArgs <> "" Then
Me.txtID = Me.OpenArgs
End If

or you can not send the id, but when the form closes do:

[COLOR=green]'set the id when the form closes[/COLOR]
[COLOR=green]'check if the id is (null or) a zero length string[/COLOR]
If Me.txtID & "" = "" Then
Me.txtID = Forms!frmCustomerDetails!txtIDcontrol
End If
[COLOR=green]'requery the calling form[/COLOR]
Forms!frmCustomerDetails.Requery

there is another way if you open the 'add' form in dialog mode. i'll leave it like this for now.

Think I'm fine with the rest of things, but if you could clarify, that would be great.

Thanks for your help

 

[vbaInet]

Just reiterating.

Using wazz's code, add this to the On Click event of the button. (Replace Me.ID with the value of your CustomerID)

docmd.openform "frmAdd",,,,,,Me.ID

then in the 'add' form's Open event (of frmAddNewContact form) you could say:

[COLOR=green]'set the id when the form opens[/COLOR]
If Me.OpenArgs <> "" Then
Me.txtID = Me.OpenArgs
End If

or you can not send the id, but when the form closes do:

'set the id when the form closes (Close event of frmAddNewContact)
'check if the id is (null or) a zero length string

If Me.txtID & "" = "" Then
Me.txtID = Forms!frmCustomerDetails!txtIDcontrol
End If
[COLOR=green]'requery the calling form[/COLOR]
Forms!frmCustomerDetails.Requery

there is another way if you open the 'add' form in dialog mode. i'll leave it like this for now.

 

[wazz]

wazz, I've noticed you always do the above when checking for null. Is it a way of iniatilising to ensure that the comparison works? Would CStr(Me.txtID) perform the same or not? Thanks

it's a short-form way of checking for null and a null (empty) string in one shot. you can't use cstr because if it is null you get a runtime error trying to convert a null.

so you could say:
If Me.txtID = "" Or IsNull(Me.txtID) Then
...

but when you use the short version (If Me.txtID & "" = "" Then), you don't have to explicitly check for null because it is never null when you do the check. It might be null in the db and even on the form, but when you do this check, you turn a null into an empty string before the check (Me.txtID & ""). so if the check is true (it is equal to "") then it must have been null.

if there was a non-null value there before doing (Me.txtID & ""), then appending the empty string to that value essentially does nothing, and the check will be false (it is not a "").

note that you can also do the reverse and check for a value:
If Me.txtID & "" <> "" Then

if (txtID & "") is not equal to "", then there was already something there (and it now has "" appended to it). if it was null, you would have 'converted' it to "".

some people do it differently, i believe they check the length:
if len(txtID) = 0 (or, <> 0) (or, > 0), etc. (do a search here for the other ways, not sure if that's exactly what other people do.) EDIT: i just saw what others do: they use: If Nz(thing, "") = "" Then... probably a neater way, actually.

in any case, be careful with length. if you're on a new record and type something into a textbox and check the length, it will be null unless you add '.text'. (if you don't add '.text', the value's length is checked (the value is the saved or initial data), and what was just typed-in is not the saved or initial data). to check the length of newly entered (unsaved) data, you have to check the textbox's text property:
len(forms!form1!txtbox1.text)

again, if you don't add .text, you are, by default, checking the value, and value = saved data, or the initial value. (on a new record, the initial value is usually null.)

 

[wazz]

you can send the id when you open the 'add' form:
docmd.openform "frmAdd",,,,,,Me.ID.

Sorry Wazz, not to sure what you mean here. What would I do with the docmd.openform stuff

the last item after all the commas (Me.ID) should be replaced by the CustomerID you want to send to the 'add' form.

so, the last item after all the commas (called 'OpenArgs') gets 'sent' to the form you open. then, the form you open (add customer) 'knows' what the CustomerID is and can use it. the 'add customer' form says to itself when it opens:
set my CustomerID control to the value that was sent to me:

Me.CustomerID = Me.OpenArgs

hth.

edit: vbaInet: lol, i didn't realize you had answered. i thought it was just a big re-quoting of the original.

 

[vbaInet]

I always use the long form, but was just curious about your method. I'm not a fan of the len function for this sort of thing. May start using your short form. Thanks

lol at least the poster now has more than enough information hehe!!

 

[gaston]

ok guys, my head is spinning, but maybe one day the rest of the content will be of use to me.

In the end I ran a macro with 'set value', and got it to pull accross the number from the orginal form.

Just going through making the frmCustomerDetails requery as we speak.

I haven't been using access for long, and this is the first time I have had to use macros, but it seems to be working *fingers crossed*

Cheers for the pointers

 

[wazz]

thanks for posting back. if you come across a term you don't know, look it up in the help files. (i usually use the VBA editor's help, as opposed to Access help, but both are... helpful.)