Null Value in Number Type Field

[psatkar]

Hi,

I have two tables, Trans_Details and Reference_Master with following fields:
Trans_Details table:
Trans_Id : Autonumber
Trans_Type : String
Trans_Ref : Number (This field has a lookup in table design with field Reference_Id field from Reference_Master table)

Reference_Master table:
Reference_Id : Autonumber
Reference_Desc : String

I have a form on which user is expected to enter value for Trans_Type and a combo-box for selecting Trans_Ref. This combo-box entry can be left blank by the user (basically, it is not mandatory to select Reference value). I have a command button on this form which when clicked will duplicate the recently entered record from Trans_Details table as a new record.

In the VBA code which I have written to assign current record values to variables, insert new record and then update field values in new record with variables works fine if Trans_Ref field is not null in the record being copied. If this field is blank (null), while setting this value to a variable, I get an error "Invalid Use of NULL". I tried using NZ(Trans_Ref) while assigning it to variable. In this case, while inserting the record (rather while updating field value with variable in newly inserted record), it gives an error that "There has to be an entry in table Reference_Master for corresponding value in Trans_Ref. Can not save record at this time".

Are there any thoughts on where I am making a mistake?

Thanks in advance,

Regards,
--Psatkar

 

[DCrake]

Simple Software Solutions

What it looks like via the error you are describing is that you are trying to create children without parents. You cannot have orphans in a 1:M relationship. You can have parents with no children, but not vice verca.

CodeMaster:

 

[doco]

It sounds to me like you are attempting to add items to your reference_master table as well. Are you?

Can you show your VB? And not sure why you are using VB to do something a well designed form would do automatically...

 

[psatkar]

Hello Dcrake,

I guess I did not explin it in the right way. Reference_Master table has two records with values Official, Personal and corresponding Reference_ids as 1 and 2. Reference_Master table has 1:Many relationship with Trans_Details table with key of Reference_Id and Trans_Ref fields. Trans_Ref field can be left blank basically allow zero length string property is set to Yes).

Using the form, I am creating new records in table Trans_Details and user need not select any value from combo-box which is populated with records from table Reference_Master. Using the command button on this form I am duplicating a record in Trans_Details table and facing the above error.

Thanks...psatkar

 

[psatkar]

Hello Doco,

I am not entering any records in Reference_Master table. I only have a combo box on this form which is populated using records om Reference_Master table. I am inserting records only in Trans_Details table.

Following is my code....Apologies that I could send it using Code/Codec format...it is in text format...

If Me.Trans_Type = 3 Then 'If original transfer transaction is Transfer Out
With varTransaction
.Type = 6 'Contra transaction type has to be Transfer In
.Ref = Nz(Me!Trans_Ref)
End With

DoCmd.RunCommand acCmdRecordsGoToNew
'Update fields with new values
With varTransaction
Me.Trans_Type = .Type
Me.Trans_Ref = .Ref
End With

Thanks....psatkar

 

[psatkar]

Hello Doco,

I am not entering any records in Reference_Master table. I only have a combo box on this form which is populated using records om Reference_Master table. I am inserting records only in Trans_Details table.

Following is my code....Apologies that I could send it using Code/Codec format...it is in text format...Resending with Code Tag....

   If Me.Trans_Type = 3 Then 'If original transfer transaction is Transfer Out
        With varTransaction
            .Type = 6 'Contra transaction type has to be Transfer In
            .Ref = Nz(Me!Trans_Ref)
        End With
 
        DoCmd.RunCommand acCmdRecordsGoToNew
        'Update fields with new values
        With varTransaction
            Me.Trans_Type = .Type
            Me.Trans_Ref = .Ref
        End With

Thanks....psatkar