VBA Insert Fails with No Error Message

**yawnzzzz** · 04-25-2013, 11:30 AM

Hi,

I'm doing an insert to a table using VBA. Sometimes the coding fails to insert, but it doesn't throw an error in the VBA. If I manually insert a record into that table, I can see that an auto-incremeted field has had a number consumed even though there's no associated record.

My coding looks like below:

1. Insert into Address table (Always succeeds)

Please Login or Register  to view this content.

2. Query newly added Addresses ID (Always succeeds)

Please Login or Register  to view this content.

3. Insert into People table (Sometimes fails)

Please Login or Register  to view this content.

*Note that above insert statements are simplified examples, but I have debugged the queries and verified there's no syntax issues, and the addressID returned from step #2 is accurate.

**Norie** · 04-25-2013, 11:38 AM

What happens when you run the queries directly in Access?

**yawnzzzz** · 04-26-2013, 01:43 PM

This is my first dive into Access, so please excuse the ignorance, but where do you mean? The VBA is within Access, so I'm guessing there's some way to easily run an ad hoc query outside of VBA.

**alansidman** · 04-26-2013, 02:40 PM

Try building the queries and running them in the Query Builder. You have not indicated what version of Access you are using (putting this in your profile helps us to answer your issues), so I cannot give you the steps to do this. It varies slightly in the UI on how to find the Query Builder depending on which version you are using.

In the meantime, you might look at this site on building queries.

http://www.databasedev.co.uk/queries.html

**yawnzzzz** · 04-26-2013, 05:00 PM

I'm on Access 2007. I found the query builder and found out that I have a validation rule violation.

I have a Text Field where it throws a validation error if I try to insert ''. If I use NULL or 'anyvalue', it works. Are blanks not allowed in Access or is there a setting where I can change this?

**yawnzzzz** · 05-06-2013, 04:07 PM

Does anyone know why Access would throw a validation error on inserting a blank value? I can put checks everywhere to switch to NULL, but I'd like to understand if that's my only option.

**vikas.bhandari** · 05-10-2013, 11:17 AM

Blank would work if you have allowed in the table to accept blank values. Set the allow Zero length to 'Yes'.

Untitled.png

HTH,
Vikas B

**Norie** · 05-10-2013, 11:24 AM

Text fields have a propery Allow Zero length which determines if a null value, eg '', are allowed.

By default it's set to Yes.

**yawnzzzz** · 05-20-2013, 04:25 PM

Thanks for the responses. The Allow Zero Length was set to No for two fields.

Note to Mod: This thread is Solved, but it's not showing me the Edit button on the first post (although I can see it on all of my other posts).