Data Type Mismatch Error

[tbbrown32]

The best query I have matches what I put in the code, and it kicks back the same error. I haven't been able to get on to work fully yet. I'm wondering if the problem is that I am trying to write the value ClassificationID to the Incidents table by joining on that same field. Since Access doesn't know that the record in the Incidents table and the corresponding record in the Classifications table are related (because the FK ClassificationID hasn't been inserted yet), is that possibly causing the issue?

Basic table structure of the one to many relationship is:



tblIncidents
-InternalIncidentID (PK)
-Some other irrelevant fields
-ClassificationID (FK)

tblClassifications
-ClassificationID (PK)

So when I try and use this it says data type mismatch

"INSERT INTO tblIncidents (ClassificationID) SELECT tblClassifications.ClassificationID FROM tblClassifications INNER JOIN tblIncidents ON (tblClassifications.ClassificationID = tblIncidents.ClassificationID) WHERE tblClassifications.ClassificationID = '" & Me.Combo702 & "';"

[tbbrown32]

Thanks Bulzie!

I gave it a shot but I get a "too few parameters" error. ClassificationID is numeric too

[JoeM]

I'm wondering if the problem is that I am trying to write the value ClassificationID to the Incidents table by joining on that same field. Since Access doesn't know that the record in the Incidents table and the corresponding record in the Classifications table are related (because the FK ClassificationID hasn't been inserted yet), is that possibly causing the issue?

Yes, I think that is it.
How can you join on something that does not exist yet?
Why do you have that join in there at all?
If you are just trying to Insert a new record into your Incidents table, how is the Classifications table involved at all?
Shouldn't it just be something like:

"INSERT INTO tblIncidents (ClassificationID) VALUES ('" & Me.Combo702 & "');"

which will just create a new record in the tblIncidents table with whatever value you are putting in your ComboBox in the ClassificationID field.

[tbbrown32]

I used an INSERT INTO SELECT statement because the tblIncident.internalIncidentID PK is populated first and the record is created. After that the user goes in an selects the ClassificationName from the dropdown which stores the ClassificationID as a FK in the Incidents table. When I do the INSERT INTO without the SELECT piece, it tries to insert a whole new record. I just want to INSERT INTO a specific field in a preexisting record, which is why I'm having so much trouble. You're correct that I can't base the SQL statement off of something that doesn't exist yet, but I'm at a loss as to what to base it off of now.

[Bulzie]

Yep along with what JoeM is saying, if you are updating a record, you need a where clause or join to know which record to update. If you are Inserting a record, you just need to populate the PK(unless it is autonumber so does itself) along with any other data values you want to add to that new record.

Another thing is to pay attention to is if your variable values in your Where or Joins are text or numeric as they have to match and the syntax in your code is different. For example:

"Select * from Table1 Where Textfield = '" & me.TextValue & "'" (text value - that is single quote, double quote & me.combobox1 & double quote, single quote, double quote)
"Select * from Table1 Where Numfield = " & me.NumericValue (numeric value)

[Bulzie]

On the form where they create the main record in tblIncidents, can you add the combo box there so it saves that ClassificationID value in the Incidents record at the time the record is created? Also if you are trying to update the record, you need to use an Update statement, not Insert.

[tbbrown32]

Thank you both for the help! I was able to create a dropdown that writes the value to the table via the combo box wizard tool. When I select the option from the dropdown, it now writes to the table. My only persistent issue is that the dropdown one level up in the hierarchy that determines what Classification choices I have does not update as I move from record to record. Hopefully I will be able to figure that one out, but if not I may create a new post for some help (unless I'm supposed to add it on to this thread). Not sure what is correct.

SELECT tblClassifications.ClassificationID, tblClassifications.ClassificationName, tblClassifications.ClassificationTypeID
FROM tblClassifications
WHERE (((tblClassifications.ClassificationTypeID)=[Forms]![frmNewMain]![Combo700]))
ORDER BY tblClassifications.ClassificationName;

[tbbrown32]

Thanks Bulzie, that is what I ended up doing. I added the combo box there and it saves to the Incidents record once I select the correct option. It also changes according to what record I display, which is good. The only issue I have, and this is hard to explain, is the combo box that determines the values for my main combo box (the one that works) does not update as I scroll through the records. The current setup is I have a ClassificationType that is selected in a combo box first, and on update this requeries that ClassificationID combo box so that certain values are displayed. The 2nd combo box updates by record and writes to the table, but the first combo box does not update. Convoluted explanation, but hopefully this makes some sense... Per JoeM's suggestion, I created a fully functioning query that works, but doesn't change the combo box value when the record changes.

"SELECT tblClassificationTypes.ClassificationTypeName 
FROM (tblClassificationTypes 
INNER JOIN tblClassifications ON tblClassificationTypes.ClassificationTypeID = tblClassifications.ClassificationTypeID) 
INNER JOIN tblIncidents ON tblClassifications.ClassificationID = tblIncidents.ClassificationID WHERE tblIncidents.InternalIncidentID = '" & Me.InternalIncidentID & "';"

[Bulzie]

Look at maybe the OnCurrent event of the form to change the values in a combo box based on values in the currennt record, etc. or the Got Focus of the combobox field to manipulate the list.

[tbbrown32]

So far, the OnCurrent event or the Got Focus option haven't worked, but I will keep trying. Thanks for the suggestions!

[Bulzie]

So you have a combo box on a form and when you move to the next record, there is a field on that form that has a value and depending on this value, it resets the combo box list values?

Basically If you want something to happen after a value in a field or combo box is selected/entered, then look at the trigger Events of that field. If you want something to happen before or after the record is saved, then look at the trigger events of the form. Access gives you alot of flexibility to control how the data is entered and manipulated.

[tbbrown32]

Basically that's the set up. I have cbo1 that determines values in cbo2. Cbo1 is based off of table1, cbo2 off of table2, and the main page they are on is a form for table3.

Table1 is 1:M to table2 which is 1:M to table3. The form is based on the PK for table3. This means that cbo2 is easy to get to update and write to table3 as a FK as I change the record from table3.

However, I cannot get cbo1 to update as I change the record. I have tried on_current, after update etc. I think part of the issue may be that when cbo1 is changed, it must requery cbo2 to refresh the values in that list. For some reason, that seems to be preventing me from successfully update cbo1 as the table3 record changes.

[Bulzie]

What criteria is cbo1 based on. We know cbo2 criteria is based on cbo1 right? So is there a field on the form that dbo1 is filtered by as you move to a new record?

[tbbrown32]

Table structure is below... Cbo1 is based off of a query on tblClassificationTypes. Cbo2 is based off of a query on tblClassifications. Once a value is selected in cbo1, it requeries cbo2 to get the corresponding options. Once an option isselected in cbo2, it writes to tblIncidents. Both cbos reside on a form basedoff of the InternalIncidentID.

tblClassificationTypes
ClassificationTypeID (primary key)
ClassificationTypeName

tblClassifications
ClassificationID (primary key)
Other fields…
ClassificationTypeID (foreign key)

tblIncidents
InternalIncidentID (primary key)
Other fields…
ClassificationID (foreign key)

[Bulzie]

So what exactly is not working with cbo1, is it not updating its list as new criteria is applied or is the value in that field not saving to the record?