Frustrating adding records

[NigelShaw]

Hi,

this is really frustrating me as im sure its right in front of me but i cannot see it. i am adding a recordset to a table from a form via code. not every box in the form requires information ( its optional ). here is my code-

Private Sub EnterRecord()
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("Contractors", dbOpenDynaset )

With rs
    .AddNew

    .FirstName = Nz( Forms!FrontForm!FirstName, Null )
    .LastName = Nz( Forms!FrontForm!LastName, Null )
    .CompanyName = Nz( Forms!FrontForm!CompanyName, Null )
    .Position = Nz( Forms!FrontForm!Position, Null )
    .StrtDate = Nz( Forms!FrontForm!StrtDate, Null )

    .Update

End With

rs.close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

if ALL boxes contain data, the record is saved but if any of the boxes are left blank, nothing is saved. i have tried

    .FirstName = Nz( Forms!FrontForm!FirstName, "" )
    .FirstName = Nz( Forms!FrontForm!FirstName, 0 )
    .FirstName = Forms!FrontForm!FirstName

but i just get the same.

am i missing something here?




regs


Nigel

 

[DevastatioN]

I just tested your code in a small sample with:

Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

With rs
.AddNew
!FIrstName = Nz(Forms!Form1!FIrstName, Null)
.Update
End With

and it works for me. I am using !FirstName instead of .FirstName though, with .FirstName it says method of data member not found, which is odd.

Try with the "!" symbol, also try your code out of the With Statement by trying rs("FirstName").Value = etc. and see if that changes anything, maybe we can narrow down the problem. Your Nz statement looks right to me.

EDIT: I just used your exact same code and it's working for me, maybe you can narrow it down, all I did was change your . to ! You may also want to add checks to see if all the fields are blank, then don't add an entry to the table.

 

[NigelShaw]

Hi,

actually, i was being a bit of a dunce. my code is using the ! instead of the . its just the way i wrote it in by mistake.


i will update later when i have found the problem



Nigel

 

[boblarson]

Not sure I understand why you are using the NZ function to pass a null when a null would be passed anyway if it was null. It seems kind of counter intuitive to replace something with the same thing.

 

[NigelShaw]

Hi Bob,

when i used

Nz(Forms!FrontForm!FirstName, "")

it didnt seem to work. as far as i could see, the "" gave me problems for whatever reason. adding the Null seems to ensure a Null was passed. Using the above made the code stop on an empty field.


regs

Nigel

 

[boblarson]

Hi Bob,

when i used

Nz(Forms!FrontForm!FirstName, "")

it didnt seem to work. as far as i could see, the "" gave me problems for whatever reason. adding the Null seems to ensure a Null was passed. Using the above made the code stop on an empty field.


regs

Nigel

What did it do if you did this:

!FirstName = Forms!FrontForm!FirstName

 

[NigelShaw]

Hi Bob,

it did exactly the same which was stop when it tried to get the value from an empty textbox.

Nigel

The Null version seems to be working fine now


Nigel

 

[Banana]

It doesn't sound right to me. Like Bob, I wouldn't even bother with Nz() if I wanted to pass a null value. It's kind like telling a kid to get out of his dirty clothing and so he can get in the dirty clothing.

Are your fields set as required, ZLS allowed, default value, or contain validation rule?

 

[NigelShaw]

Hi Bob / Banana,

i will try your methods then and remove the Nz(). i dont have any validation going on but im sure its something very simple. I'll print it out and look at it from a different view!


regs,


Nigel