Passing Field Values 1

[LittleMan22]

Hi,

I have 2 questions:

a) I have a field on a pop-up form that calls a value from another form with the default of the pop-up set to: Forms!frmClients!pgeAdditionalInfo!MaidenName. It works beautifully, except when there is nothing entered in the MaidenName field on the origional form - in this case a '0' shows up on my pop-up form. How can I get around this '0'.

b) I want the values from the field on the pop-up form to transfer back down onto the main form for certain fields. i.e. if on the origional form MaidenName is blank but someone enters something in this field on the pop-up form I want the value to be 'passed' back down to the origional form. I need some type of code in the after_eventprocedure to accomplish this. I just don't know what the code should look like...

If anyone could help me out I'd really appreciate it.

Ryan.

 

[Craig0201]

1) Use Nz on the maiden name and you'll be able to sort that. Would be worth checking the default value of the control or bound object too.

As for 2)........pass!!

Craig

 

[jgoodman00]

Is MaidenName a combobox to some sort of lookup table? If it is, it will probably be an integer datatype, with a default value of 0. If no maided name is select at record creation time, the default of 0 would be appended. If this is the case, removing the default should solve this problem. You might need to remove this default from the form control as well.

It sounds like the popup form is an additional form which is used to add new Maiden names to a lookup table. If this is the case, you need to requery the combobox on the original form when it regains the focus. (me.requery)

If this is not the case, it would help to describe the structure in more detail, so as to better understand what is happening... James Goodman

 

[LittleMan22]

I tried using Nz (i.e. = Nz([Forms]![FrmClients]![pgeAdditionalInfo]![MaidenName]) but to no avail. I still get the '0' when I attempt to call the field value. The defaults are not set to '0' either and the field is not numerical so I don't really know how to get around this.

jgoodman00: I don't understand when you say requery. The pop-up form is just an additional form that is used to enter client information before we run an app (through a module linked to a PDF form). When data is entered into the pop-up form it is simply held as the current record in a query to subsequently populate the fields on the PDF. What I need to do is get it so that when the occasional field is entered in the pop-up (a field which we also have on the form underneath) the data will be 'passed' or 'copied' from this field to the other.

Any ideas?

 

[jgoodman00]

In that case set the variable manually:

e.g.
[Forms]![frmClients]![MaidenName] = [Forms]![frmClients]![pgeAdditionalInfo]![MaidenName]

This will simply assign the value in form2 into the specified field in form1. I think this is what you mean...


James Goodman

 

[LittleMan22]

jgoodman00:

The code won't run. I get the error message 'Object doesn't support this property or method' (RuntimeError 438).

This is what I have written as per your example:

Private Sub MaidenName_AfterUpdate()
[Forms]![frmClients]![pgeAdditionalInfo]![MaidenName] = [Forms]![frmApplicationTrans]![MaidenName]
End Sub

I don't know why it's not working. Another alternative could maybe be thatI could have code that would automatically save something entered into a field like MaidenName in the appropriate table (in this case tblclients). Maybe if there was some kind of code written into the afterevent procedure that would store the field value in this table, the field on the main form would update automatically?

Do you, or anyone else, have any ideas if this is possible, or how to go about it?

Ryan.

 

[jgoodman00]

Are you not trying to assign the value in pgeAdditionalInfo.MaidenName to frmApplicationTrans.MaidenName? If so, you have the two references the wrong way around.

So if pgeAdditionalInfo contains the value, the assignation should look like:

[Forms]![frmClients]![MaidenName] = [Forms]![frmClients]![pgeAdditionalInfo]![MaidenName]




The code should also be executed in the after_update event of the pgeAdditionalInfo form...

James Goodman

 

[LittleMan22]

I think I know what the problem is. I can pass the value down to another field that is on the main form, but can't pass it down to the MaidenName field because this field is under a tab in the main form.

So this is what I have for the code:
[Forms]![frmClients]![Other Info]![MaidenName] = [Forms]![frmApplicationTrans]![MaidenName] and it's in the after update event procedure of the frmApplicationTrans form.

How can I call a value from within a tab?

Thanks again,
Ryan.

 

[jgoodman00]

By tab I presume you mean tab control? If this is the case, do not include the tab name ([Other Info]?) within the reference... James Goodman

 

[LittleMan22]

That works beautifully.

Thanks a ton for your quick help. I appreacite it a lot.

 

[LittleMan22]

jgoodman00:

I've got a new question that's come up

I've got it to work, however, in attempting to pass teh value, if the value is null then I get the message 'Data type conversion error'. This only happens for numeric fields like income and net worth. A blank field doesn't affect other fields that are text. How can I change the code to get around this error when I have numeric fields?

Thanks, Ryan.

 

[jgoodman00]

You should be ok to use the Nz function for this purpose.
e.g
[Forms]![frmClients]![Income] = Nz([Forms]![frmApplicationTrans]![Income], 0)


Hopefully this will stop null from propogating & replace it with a 0....
James Goodman

 

[LittleMan22]

The problem is though that I have trapped the error in the code and it's on the command button that opens the pop-up form. So putting your code on the onload event procedure won't work because Access returns an error previous to its running. Here's the code for the button (I have marked the 'trouble spot' with **):

Set rst = CurrentDb.OpenRecordset("SELECT * FROM ApplicationData WHERE PolicyID = " & Me!PolicyID)
With rst
If .EOF Then
.Close
DoCmd.OpenQuery "AddClientToApplication"
Else
Set rst2 = CurrentDb.OpenRecordset("qryClientPolicyApplicationData&quot
With rst
.Edit
For Each fld In rst2.Fields
strTest = Nz(rst2.Fields(fld.Name), " &quot
'Debug.Print fld.Name, strTest
**.Fields(fld.Name) = strTest**
Next fld
.Update
.Close
End With
rst2.Close
End If
End With

Select Case Me!Carrier
Case "Trans"
DoCmd.OpenForm "frmApplicationTrans"
Case "AIG"
DoCmd.OpenForm "frmApplication"
Case Else
MsgBox "No application for " & Me!Carrier
End Select

Do you have any idea how I can change the code to accept a null value?

Thanks again,
Ryan.

 

[jgoodman00]

I just checked, & assuming the field is not set to required, null is allowed for this data type. In your code above, the problem is that you are trying to assign [Space, ASCI 32?] to a number field. This is not allowed. However null is. I therefore suspect that your problem is therefore that you are not passing Null through the expression, rather a zero length string or similar.

You therefore need to make sure the value which it errors on is (Is) Null. Officially, Null is 'unknown'. We interpret this as empty. However we also interpret a zero length string, or a string with just a space in it as exactly the same. However they are not.

I have had a quick play with a couple of forms, & Null can propogate through such an expression (or sub in this case).

So, my only advice can be to make sure these fields are Null, (if necessary set them to = Null), & make sure the field you are trying to update is not a 'required' field....


James Goodman

 

[LittleMan22]

I think that you've hit it exactly right when you say that they are merely zero-length strings. How do I set their values to = Null though?

Ryan.

 

[jgoodman00]

Well, in theory if you don't assign it a value, null will be the value propogated (after all if the user doesn't modify the value stored in a field, & no default is set Null is the 'value' which will propogate).
Therefore you shouldn't actually need to set their values to Null.

Do you have code anywhere in your form which is currently modifying the values in the form which is donating the values? If so, I suspect this could be the problem, whereby these fields are being assigned a zero length string, rather than being set to Null. To correct this, you need to look for any occurences within your code which assign a control (or field if it is a query) either "" (zero length), " " (space) or similar. These need to be corrected & set to = Null . This is a readily simple assignation.
N.B. To search for values which are Null, the syntax is Is Null. Entering = Null will not work, because if Null is unknown it cannot be equal to it (kinda confusing concept!).

Hopefully this will be where the discrepancy is being generated & you can use find & replace or something to sort this out. I would recommend doing this firstly. I cannot think of a single situation in which entering a zero length string would actually be beneficial (perhaps someone else can). Instead either a genuine value, or Null should be the value stored...

James Goodman

 

[LittleMan22]

I know what the problem is. Origionally when the database was created the default values of income and networth were set to 0. As the databased became more advanced and we began writing code that would transfer the client data onto various applications we didn't want values of 0 showing up under a clients income and net worth. There were many times when we just didn't have this info so we deleted the '0' from the field. Now the problem is though that the field isn't null - it's a zero-length string. So how can I change it back to being null? I can change the default value but isn't this too late - many of the forms have already been played with in that their values have been changed.

What I almost need is some code written into the command button that opens the pop-up application that says something like:

If Me.Income = " " Then
Me.Income Is Null
End If

This code doesn't do the trick though...do you have any other ideas?

Ryan.

 

[jgoodman00]

That code wont work for 2 reasons. Firstly, you are not testing for a zero length string. The above code would test for the space key. A zero length string is "".
Secondly, you assign a field the value of Null by using = Null

It should therefore read:

If Me.Income = "" Then
Me.Income = Null
End If

However, why dont you create an update query which would remove this problem from the source tables once & for all?? James Goodman