valiadation rule violation (again)

[AndyCompanyZ]

I have a update query running from the afterupdate on a cascading combobox which is really throwing me. The query is very basic:
INSERT INTO tblEvent ( VenueRoomID )
VALUES (Forms!frmEvent!cmboVenueRoom);
but it gives me the above error. I have checked all the usual violations like required field indexing etc. but none are set. The other issue is that if I allow the update anyway (by saying yes to the question if I want to run the query anyway) it does update the table. However when I go back to the form with the same record it deletes it from the table. I presume this is something to do with the combobox requerying each time. I also have a field on the form which should show the record that has been already saved in the table with the control source of VenueRoomID this fills in when I select the room in the combobox but disappears when I return to the form for the instance of the Event because the table entry is cancelled by the combobox. Can anyone tell me what i am doing wrong please.

 

[GinaWhipp]

I think the first we should clear up is... did you mean APPEND query? Because and UPDATE query says *UPDATE* not *INSERT*.

If the above is the query you are running then yes you will get violation errors and the record is probaby not appending if one of those fields is a Primary Key as it would cause a duplicates.

 

[AndyCompanyZ]

I'm not sure if it should be an update or append as it is supposed to just insert the value into the table. is that an update or append

 

[GinaWhipp]

Okay, then it would be an APPEND query. Then you will get that error because you are trying to insert a Primary Key which does not allow for duplicates.

Now, after reading your original post several times I still don't understand clearly the issue besides the Validtion Error. Please describe what you are trying to do and then what is happening.

 

[AndyCompanyZ]

I have a form which has 2 cascading comboboxes on it. The first selects a venue and the second (unbound) shows all the rooms at that venue. This works fine. I then just want the user to select one and put that value into a field (number) on the tblevent table and store it but what is happening is that when i run the query from the afterupdate of the 2nd combobox it runs the query and then tells me that I have validation rule violation on 1 record. If I select run query anyway it puts the record into the table. I can go through all the records and select a venueroom and it populates the table but when I go back to the record it replaces deletes that record or replaces it with whatever i have selected. I need to pull up that record and place it in the text box I have on the form to show that a venue room has been scheduled and the user doesn't need to select another. But I can't pull the record up because it is always cancelled whenever i go back to the form.

 

[GinaWhipp]

Okay, well let's see if I get this... You want the combo box on the form to go to the newly appended record? Is that what you are saying? If that is it then you're going to need more then a query, it's going to require some VBA. (At least, that is the only way I know to do it.)

As for the validation error, I don't see how it is adding anything but if you say it is then it is BUT the only way to make the error go away is to copy/paste the error so we can see which field is causing it and tell you how to *fix*.

 

[VilaRestal]

Forms!frmEvent!cmboVenueRoom
looks like the name of a combobox, not a field

try it with
Forms!frmEvent.cmboVenueRoom.Value

(all other possible problems aside)

 

[AndyCompanyZ]

VilaRestal I tried .value as well but same problem
GinaWhipp I'm sorry I'm not explaining very well. I just want the combobox to update a field in a table with what has been selected. And then I want what has been put in the table to stay there and be able to put it into a textbox using that field as the control source so that when i return to the form for that particular event it shows there has been a room scheduled and the user doesn't need to select one.
I have included some screen shots to show in scnshot1 the error but it doesn't say much in scnshot2 the data in the table after the query and scrnshot3 the table field properties.

 

[VilaRestal]

Note
Forms!frmEvent.cmboVenueRoom.Value

Not
Forms!frmEvent!cmboVenueRoom.Value

There's a difference: The first refers to the combobox's value the second to the value of a field called cmboVenueRoom

 

[spikepl]

I would strongly suggest not to rely on dot vs bang to discern between fields and controls. The issue is not straight forward (see this discussion http://www.access-programmers.co.uk/forums/showthread.php?t=202806 ) .


Code relying on a pretty obscure and not always consistent feature like dot/bang this will quickly become endebuggable for even the author.

Stick to accessing controls on the form and binding fields to the controls. This provides a consistency that will allow you to write maintainable code.

 

[AndyCompanyZ]

Ah I see the difference now but it still gives me the same error. I'm wondering about what was said about PKs. The value of the combobox is a PK but the destination field isn't is that an issue.

 

[GinaWhipp]

The error is definitely a PK issue and the only way to fix that is to remove that from the APPEND query.

The screen shots helped but really would help if you could upload a stripped down version of the database. Because I don't know whether you need a new query, VBA or just a simple line added somewhere to give you what you want.

 

[AndyCompanyZ]

I've had enough of this today. I am so close to finishing this db but this is proving the hardest part. I took out the PK from the query and used instead the name of the venueroom which isn't ideal but if that's the only way then so be it. It still comes up with the same error though. I'll try and load a stripped down version but I tried doing this before and it had so many connected tables and subforms it was nearly the entire db and I don't want to put the who;e thing on line as it includes sensitive data and I have a NDA in operation.

 

[GinaWhipp]

I was hoping you had one with little or no data which is what I meant by stripped down.

If you have time before you are done for the day copy/paste the entire query SQL here so we coan have a look.

 

[VilaRestal]

I would strongly suggest not to rely on dot vs bang to discern between fields and controls. The issue is not straight forward (see this discussion http://www.access-programmers.co.uk/forums/showthread.php?t=202806 ) .


Code relying on a pretty obscure and not always consistent feature like dot/bang this will quickly become endebuggable for even the author.

Stick to accessing controls on the form and binding fields to the controls. This provides a consistency that will allow you to write maintainable code.

My point was if there is a control called cmboVenueRoom but NOT a field called cmboVenueRoom (and going by the name of it I suspect that is the case) then

Forms!frmEvent.cmboVenueRoom.Value

will work but

Forms!frmEvent!cmboVenueRoom.Value

won't (it's looking for a field that doesn't exist)

 

[VilaRestal]

But then again, having read the Me! vs Me. and testing it - yes it does work.

I wouldn't trust Access to figure out what it should do everytime

 

[VilaRestal]

But I would say in response to

Code relying on a pretty obscure and not always consistent feature like dot/bang this will quickly become endebuggable for even the author.

Stick to accessing controls on the form and binding fields to the controls. This provides a consistency that will allow you to write maintainable code.

Whatever you do you're relying on dot or bang

And having every field represented by a control is overkill when a bang before the field name will work

 

[AndyCompanyZ]

I know it must be a PK problem now because I tried (just messing about) to link the tables in the relationships from the PK of the VenueRoom table (which is the VenueRoomID) to the VenueRoom field in the Event Table (which is where I want to store it) but it wouldn't let me even try as the tbale scrolled whenever i tried that join but would allow me the other way from the event table to the the PK. I think I may have to rebuild the table so that the ID of the room is not the PK but isn't that wrong too.

 

[AndyCompanyZ]

I have attached the db with data stripped out and dummy data put in for anyone to see if they can find the problem it is in 2010 though so maybe no one can see it. The problem I'm having is on frmEvent on the VenueRoom combobox.

 

[GinaWhipp]

Having a look now...