INSERT with optional fields

[Hakello]

Hello,

I currently have a bound form that adds a new student, however, I've decided I don't like bound forms as weird stuff can happen (like when the user exits in another way than I anticipated) so I'm making an unbound form which will add the student when a button is clicked. I already have this working on some other places so this should be no problem anymore.

However in my database a lot of student information is optional and only a first and last name are required (and ofcourse an autogenerated PK). So my question is how do I deal with these fields that might or might not be empty?

I could make a fairly big If ... ElseIf... construction where I slowly fill a String with all the optional fields but I was wondering if maybe there are some better ways to approach this?

 

[pr2-eugin]

Hello Hakello, Not sure why you are going through with so much hassle of coding, when Access can do with simply binding them to the appropriate table.. Access is not designed for Unbound tables.. Though you can achieve this you still are going to do a loooooooot more work... To answer your question.. You can just use a regular INSERT INTO, if the values are available it will be inserted else an empty string will be used..

INSERT INTO yourTableName(fieldName1, fieldName2, filedName3, fieldName4) 
VALUES ('" & Me.FirstNameTxt & "', '" & Me.LastNameTxt & "', '" & [B]Nz([/B]Me.OptionalAddress[B], vbNullString)[/B] & "', " & [B]Nz([/B]Me.OptionalAge[B]) [/B]& ")

If there is some value in the optional boxes they will be added, else they will take an empty string in that place..

 

[Hakello]

I...hadn't actually tried using and empty variable as I simply assumed it would give an error, thanks

 

[Hakello]

Ok thanks, always good to know, going to add some checking code to some beforeUpdates!

My main problem however was that when a required field is left empty it fires a "You must enter a value in the ... field" and I'm having issues with catching it. Catching runtime errors works great (like when trying to insert sql ) but this is a rather frustrating pop-up that keeps bugging you even if you try to undo whatever changes you made. I tried the field onDirty, lostFocus and beforeUpdate and the form onDirty and beforeUpdate and none of them work with this code: (I figured I could catch this when trying to save)

If Me.groepcode = "" Then
Me.groepcode = "EMPTY"
End If

The buttons the user uses check if correct data has been filled or undo all changes if cancel is clicked. But this popup fires before any of that can happen. I rather not remove the 'required' property so I figured I'd switch to unbound forms.

 

[CJ_London]

sounds like the required property in your table is set to true when in fact it is not a required field

 

[Hakello]

Hm? I don't really understand, why would you assume it is not required?

I allow users to edit the data they entered, but there needs to be a certain minimum of information for the record to be usefull, thus the code (PK) is required. I could change it to have an autoincrement PK and make the code not required and then check it everytime it is edited on a beforeUpdate or whatever, but that wasn't my question

I was simply having some trouble with catching the errors that bound forms give while the runtime error's are easy to intercept so I changed them to VBA so I can give a popup with text to users on what they have to change.

If you know how to catch errors generated in bound forms that would be great though!

 

[CJ_London]

Hm? I don't really understand, why would you assume it is not required?

because you said this in your original post

However in my database a lot of student information is optional and only a first and last name are required (and ofcourse an autogenerated PK). So my question is how do I deal with these fields that might or might not be empty?

However now you are saying

I could change it to have an autoincrement PK

So I guess I'm now confused!

If you know how to catch errors generated in bound forms that would be great though!

I handle it by minimising the use of field properties in the table and handling them all within a form - in particular I don't use lookups, caption, validation, format, input masks, text align or required and handle it all within the form. (I do use default, indexing, and normally have allow zero length string=yes). I also don't use multi select or calculated data types - which are only available in Access - which for me limits the opportunity for upsizing without quite a bit of extra work.

The rationale is that although using these in the table will replicate when the form is created (so in theory a quicker/easier development time), you often want something different on the form and the properties I don't use can muddy the water as to what the data actually is (in particular using lookup and format) leading to longer development (i.e. debug) times until you slap your forehead and say, 'I forgot I did that'.

 

[Hakello]

(I do use default, indexing, and normally have allow zero length string=yes)

This works! So simple yet so brilliant Now I can incercept zero length strings on unload/button click without access bothering me!

Ah yes sorry, I mixed up, my original example was about students but I already solved that by making it unbound, so I used another example in my third post which is about another form with a similiar problem

 

[CJ_London]

Got there in the end

 

[Hakello]

I tried the field onDirty, lostFocus and beforeUpdate and the form onDirty and beforeUpdate and none of them work with this code:

If Me.groepcode = "" Then
Me.groepcode = "EMPTY"
End If

I tried to catch it there, but the error was given even before the (bound) form would close, in fact as soon as the field in question lost focus it fired this error. This was solved by setting the allow ZLS to yes and then catching it on beforeUpdate

I kinda figured it would be Null when a certain column never had a value or was deleted but it would be a ZLS if it had a value that was cleared with backspace or delete. It seems... inconsistent that deleting a value with a backspace key gives a different result than doing so with the delete button, does anyone by any chance know the reasoning behind this? (just curious)

But you are right, I absolutly do not want ZLS in my tables, so that is why I intended to check for them before the data is saved (or well, seeing its a bound form, revert it to its original state if the user doesnt fix it)

What does *** If Me.Myfield & "" = "" Then *** the bold part do exactly? does this check for spaces?

Thanks! We're slightly offtopic from the original post but I'm learning a lot

 

[pr2-eugin]

The & "" turns a Null Value to a Zero Length String.. Null is not a value, it cannot be compared against anything.. Null <> "" Or in other words Null <> ZLS.. So the following tests will fail..

Null = ""
Null <> ""
IsNull("")

I always use this strategy

Len(Me.controlName & vbNullString) = 0

to test.. As it always traps both Null and Zero Length Strings..