Append query with check boxes

[kidrobot]

Can someone tell me why my check box selections on my form aren't inputting into my table?


**BlueStreak and Rework are check boxes on both the form and table

INSERT INTO tblRules ( Origin, Destination, [Road to], Train, Block, Junction, [T/C], [L/E], [S/R], BlueStreak, Rework )
SELECT Forms!AddForm!txtOrigin AS Expr1, Forms!AddForm!txtDestination AS Expr2, Forms!AddForm!txtRoad AS Expr3, Forms!AddForm!txtTrain AS Expr4, Forms!AddForm!txtBlock AS Expr5, Forms!AddForm!txtJunction AS Expr6, Forms!AddForm!txtTC AS Expr7, Forms!AddForm!txtLE AS Expr8, Forms!AddForm!txtSR AS Expr9, Forms!AddForm!chkBlueStreak AS Expr10, Forms!AddForm!chkRework AS Expr11;

 

[WayneRyan]

Kid,

I generally use:

Insert Into tblRules(...)
Values (...)

But the syntax in your example is OK.

If your checkboxes are Null, then you'll get an error because it will parse as

Select As Expr11

But, you'd see an error (you don't turn off warnings do you?).

If you're not getting an error, then you must be supplying a proper 0 or -1 in your
SQL statement.

You don't say how you're invoking your SQL, can you view the text in the Immediate
Window?

That should give you some clues.

need more info,
Wayne

btw, Your syntax --> Select Control1, Control2, Control3 is a good way to get around
inserting fields with embedded quotes/double-quotes.

 

[kidrobot]

Sorry about not being so clear. I'm doing this SQL query view in the Access Window not VB. My warnings are not turned off and when I run this query I get no kind of warning.

 

[WayneRyan]

kid,

and no row is inserted?

I'll try to duplicate this.

Wayne

 

[kidrobot]

kid,

and no row is inserted?

I'll try to duplicate this.

Wayne

Yes a row is inserted, but the box fields don't "echo" into the table(in other words get checked). So if I check the box on the form it doesn't check that corresponding record in the table.

My last resort is to make the form have a combo box saying Yes/No and That should work, but I'd rather use the check box.

 

[boblarson]

When I put this into the QBE grid designer, the form references appear to need to be changed to:

Expr2: [Forms]![AddForm]![txtDestination]

 

[kidrobot]

Bob, thanks. I tried what you said but it is still doing the same thing. It inputs 9 out of 11 records the 2 left over records being the 2 check box ones. chkBS and chkRW.

 

[boblarson]

I think you may need to use an IIF or Switch statement to specify to it to put in a -1 if true and 0 if false.

 

[WayneRyan]

Kid & Bob,

(See note at end)

Using this method, I get some very disconcerting results.

Null checkboxes get converted to 0 (unchecked).
There is no way to truly represent an uninstantiated state in the table.

String values get truncated to Design specifications.

The above happen with no warnings or errors.

The only errors thrown are type mismatch. Like trying to put a string in
an numeric field.

But, with appropriate data, the checkboxes go in just fine.

I don't see how your checkboxes DON'T get inserted.
I can't duplicate that.

Bob, I thought that's how kid specified them, it looks like the same syntax.

Note: Just tried, and if you reference your checkbox improperly, it will
add the row, with an unchecked checkbox.

Wayne

 

[boblarson]

Sounds like an alternative way of accomplishing the end-result will be needed.

 

[kidrobot]

Yeh I totally wasted both of your time. I have 2 forms with different names for the same check boxes which threw me off. Sorry 100 times.

 

[WayneRyan]

Bob,

I'd tend to agree.

The willingness of Access to truncate data, and provide its own defaults
using the Insert/Select method doesn't make me happy.

Also, in a Insert/Select that actually uses a table and a Where clause,
the user gets no feedback on whether no records or a thousand records
are inserted.

Maybe using a recordset with a .AddNew is better.

Or Maybe kidrobot just referenced a checkbox that doesn't exist. That
will "default" to unchecked (or 0).

Wayne

 

[boblarson]

Yeh I totally wasted both of your time. I have 2 forms with different names for the same check boxes which threw me off. Sorry 100 times.

No problem, whatsoever. Believe me I've done the same type of thing MANY times. So don't feel bad

 

[WayneRyan]

Kid,

No problem, these things happen.

At least I got to see a way to insert data with embedded "delimiters"
and also got to see some distressing Access behaviour.

Glad it's OK,
Wayne

 

[kidrobot]

Kid,

No problem, these things happen.

At least I got to see a way to insert data with embedded "delimiters"
and also got to see some distressing Access behaviour.

Glad it's OK,
Wayne

Lol I don't know if that last part is a good thing. But thanks again guys.