Why can't I add records through my query?

[Seth Schrock]

This is one of the most complicated databases that I have worked with, so apologies ahead of time if I'm not real clear as I'm trying to only include the important details.

I have the following tables:
tblDevice (DeviceNumber is PK)
tblJob (JobID is PK)
tblDeviceJob

I have a many-to-many relationship between tblDevice and tblJob with tblDeviceJob being the join table. I have a form based on a query for tblJob with a subform based on a query that includes tblDeviceJob and tblDevice. This is how I want my query to look like for my subform:

SELECT tblDeviceJob.DeviceNumber,
tblDeviceJob.JobID,
tblDevice.Selection
FROM tblDevice INNER JOIN tblDeviceJob ON tblDevice.DeviceNumber = tblDeviceJob.DeviceNumber;

On the subform, I'm using a combo box to pull the DeviceNumber along with the ComputerName field from tblDevice. My problem is that as soon as I make a selection, I get the following error:

The current field must match the joinkey '?' in the table that serves as the 'one' side of the one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.

Everything works fine if I remove the tblDevice.Selection field from the query, but I need that field. One thing that I noticed is that when I include the Selection field, the subform doesn't have the join field populated in the next row as it normally does. When I remove the Selection field, the join field is pre-populated waiting for a new record. What can I do to fix this?

[twinnyfo]

Is your combo box to pull the Device number a bound or unbound control? The query underlying the forms may not allow you to modify the recordest. Whenever you select an item, the query doesn't know what to do, since the query is joined using that field.

Are you trying to change the device number or filter by the device number?

[Seth Schrock]

The combo box is bound to the join table and is pulling from the device table. The query does allow me to modify the recordset somewhat. I can manually add the JobID (the second field of the PK), then leave the record, come back to that record and then make a selection from the combo box. However, I had to have left the record first.

[twinnyfo]

after you update JobID, try AfterUpdate to be

Me.cboDeviceNumber.Requery
 

to see if it refreshes the data under the combo box. Although I am still not sure if that is answering your original question. We'll keep tinkering...

[Seth Schrock]

Didn't work. The combo box is populated. For some reason though, it doesn' like having a new record being initiated by that field. I would rather take the JobID off the subform (leaving it on the query for linking purposes) so that it isn't seen by the user. I have it on right now for troubleshooting purposes.

[zmbd]

Seth,
http://support.microsoft.com/kb/928023

The afterupdate event will not work here. The afterupdate event doesn't fire until the record is written to the table and the engine has already errored before the event fires because you have a broken composit primary key. This is why when you manually add the JobID as in #3 all is good... you've created the related record.

In the before update event...
You may be able to do something along the lines of pulling the new information to limbo and cancel the record save. Then pass that new information to a seperate sub call (the seperate call to force the change in scope from the event to the sub) to create the new record on the one-side of the join and then force a requery upon return from the sub. It will be tricky and I'm not sure that it will be able to be done in your database without actually looking at it; however, give it a try and let me know.

-z

[Seth Schrock]

I had actually seen that post before, but its not creating empty rows in the One side of the relationship so I'm not sure how much that post applies to me. Also, the workaround says that the problem does not occur if you project a non-complex field from the One table. I don't think that my query is complex.

I have no clue how to do what you are saying in the before update event.

[zmbd]

I don't beleave that "complex" refers to the information in the field.
Because I'm a little slow let me try to explain myself using some tinkertoys ( :) )

Tbl1
[SimplePK] autonum PK
[field1] text(50)req-nonull-nonindex
[field2] text(50)req-nonull-nonindex

Tbl2
[Complex1PK] autonum PK(1/2)
[Complex2PK] autonum PK(2/2)
[field1] text(50)req-nonull-nonindex
[field2] text(50)req-nonull-nonindex

Tbl2 has a complex primary key; thus a "complex field" where-as Tbl1 only has the simple field for the primary key.

Until the entire primary key has been established, the record can not be saved even though there maybe a pointer to it; however, the record is "empty" so as far as Access is concerned; thus, the engine errors because there is no related record on the one-side of the 1:M relationship.

Now, I know what I'm thinking; however, I'm not sure if I was able to get that into the text... sort of like my drawings... I can see such wonderful things in my mind's eye but, unlike my Mom, trying to get that on the canvas... not so pretty.

-z

[Seth Schrock]

Here is the BE and FE

Basically, from the Home page, click Jobs, then Add Devices. the form that comes up is the one that I'm working with.

It is possible that I'm doing things the hard way. Basically what I'm trying to do is to create a job, add devices and tasks and have checklist for each device. To do this I setup a three-way relationship: tblDevice many-to-many with tblTask which is related to tblJob One-to-Many, which is related to tblDevice many-to-many (with the appropriate join tables). I'm creating the Job in tblJob and then adding the devices and tasks, but this requires me to run an append query to populate the join table between tblTask and tblDevice. What I'm working on now is the ability to add devices after the job has been created, which means that I have to do an append query for just the devices that I'm adding (hence why I'm using the Selection field so that I can distinguish between those already part of the job and those that I'm adding on). Hopefully that makes sense. If there is a better overall setup, please let me know. I really want my databases to be properly setup. I am totally self taught so I have no one to check my designs.

[Seth Schrock]

The complexity thing does make sense. What I don't understand is that I'm getting the error before record is trying to save. The little pencil is still there and I haven't tried to leave the record, so why is it even trying to check the values? Usually, if you type something incorrectly, it tells you when you try to go to the next record.

[zmbd]

Yes... the behavior is sometimes the most confusing...
For some reason, it is trying to update the many side of the relationship first so it goes to the one side table and can't find the related record; thus the error.

Let me take a look at your database... may take me few moments ( :) ) I warn you now... I have the three kids at home and they seem to know when I'm playing... that's when they like to bug me (yes this is my hobby... go figure, Chemist by day... Database S&M by night... and Monty Python is the funniest stuff around)

Until I get a chance to dissect your database...


PLEASE, Please, Please take the following with the greatest amount of respect - I understand that you've experience with designing databases, I offer the following by way of "review" or "refresher"...

I noted that you said that you are self taught...

I've found that having an outline of things to do helps when I design my projects... what I found though is that in my text books and other references the basic foundation is explained very badly... Then I stumbled upon some white papers and a wonderful Oracle database engineer... Trying to put together a website to bring that together for others I ran across this http://www.applecore99.com/tut/tutindex.aspp Seems that just like "Soft-scrub" someone else beat me to the punch.

SO... in a few hours.... oh.. I need to go run an errand for the Wife in a moment too... and the kid...
Sometimes, I wish I was the dog... feed me, water me, let me chase that rabbit! SQUIRRL!!!!!

-z

[zmbd]

Seth,
I'm running into several compile errors in your code.

Are you using "option explicit" as many variables are not defined in your code.

There are muliple controls missing from the forms that are refered to in the code; however, they're not on the forms in question.

Please run the compiler on your front end and repost.

After the 15th failure to compile I just don't have the time to fix the code nor do I know which control should have what attributes etc...

-z

[Seth Schrock]

I thought I did. I always try to declare them even before I figured out how to have Access put the option explicit in for me. I was always forgetting to set it.

In going through it, the following didn't have it set:
frmDeviceSearch
frmHome
frmIPAddressManager
frmWarrantyReport
sfrmDeviceSearch
sfrmIPAddress
sfrmMonitor
sfrmWarrantyReport


Most of these didn't even have variables in any of the code, but I will make sure that I add that in when I get back to work. But all of the other forms did have it set. I haven't checked the reports.

[NeoPa]

Before Posting (VBA or SQL) Code should help with some of those basics Seth. Getting Option Explicit to come up automatically is really important in a database. It's the first thing I fix when I take on any new database.

[Seth Schrock]

I do have Option Explicit set for the section of the database that I'm having trouble with. At least I don't have to worry about it now that I have it set to do it automatically.

[zmbd]

Seth,

OK, I've spent the last 6 or 7 hours looking at your database... only on this issue.

This is the weirdest thing I've seen in access so far...
It has something to do with the subform itself.

So.
1) I appended the _OEM to your original subform.
2) I remade the subform... matching exactly everything you have in the subform for properties, code, etc...
3) I then opened your orginal master form
4) I then deleted the subform you had in the Master and then using the wizard added the subform I just created

I repeated step 4 again doing the entire adding of the subform to the orginal master... without the wizard.

In my remade form... I could add and edit records.
In the first go-around for Master/child... no errors
In the second go-around for Master/child... no errors.

What I did:
- For your combo boxes in the subform... open notepad... and then copy their record source SQL out to notepad Create from scratch the subform, using the saved SQL for the combo boxes.
Open the Master form and delete the current subform... then either by Wizard or by hand inset the new subform...

I've attached what I've done so far...
You'll need to relink your tables... I had this in the windows-xp allusers desktop subfolder.

-z

Once the number three, being the third number, be reached, then lobbest thou thy Holy Hand Grenade of Antioch towards thy foe, who, being naughty in my sight, shall snuff it.

[zmbd]

oh... the attached database in #16 is from the first posting thus it never did compile for me... however, I didn't write any code so I wasn't worried. The only difference is as described in #16

I really need a nap before the Wife finds me stuck to the keyboard
-z
King Arthur: Right. One... two... five.
Galahad: Three, sir.
King Arthur: Three.

[Seth Schrock]

Okay, I'm trying to duplicate what you did. What do you mean by "I appended the _OEM to your original subform"? I'm not familiar with OEM in this context.

[zmbd]

sorry, it was quite late/early (depending on your point if view) in the day when I made the post...

"1) I appended the _OEM to your original subform"
I renamed your orginal form by adding "_OEM" to the name. It's just a tag I'll append to a name on something when I want to keep the orginal intact while working with a copy of the item in question and need that copy to have the original's name. It's short for "Orginal Equipment Manufacture".
-z

[Seth Schrock]

After seeing the _OEM on the end of the form title, I figured out what you meant.

Also, I found the one little setting that wasn't copied over when you recreated the subform: I had the checkbox, Selection, having a default value of True. Yours didn't. When I added it to yours, I got the error again. When I took it back off, it worked. So now I'm going to play with saving the record and then setting the checkbox to true in the after_update event of the DeviceNumber combobox.

[Seth Schrock]

I got it to work with the following in the After_Update event for DeviceNumber:

Option Compare Database
Option Explicit
 
Private Sub DeviceNumber_AfterUpdate()
If Me.Dirty = True Then Me.Dirty = False
If Not IsNull(Me.DeviceNumber) Then
    Me.Selection = True
End If
 
End Sub

I'll use this unless you think that there is a better way. Thanks for all of your time. I don't think that I would have figured out the Default Value thing if I hadn't had your redone subform.

[zmbd]

I have no idea why that default value would be an issue... I'd check as to where else is that field used in your table relationships... somewhere that field may be on the many-side of one of your relations between tables and the record there isn't made until the jobid is entered...

The afterupdate event is a good workaround until you figure out the field.

-z

[Seth Schrock]

By "that field", do you mean Selection or DeviceNumber? Selection has no relationships (I'm not even sure if a yes/no field can be used to create a relationship) and DeviceNumber is what relates tblDevice to tblDeviceJob with tblDeviceJob being the Many side (which is what the main form is based on).

The only thing that I can think of is that the default value is getting posted before the JobID is added, even though it looks like it is done at the same time. If this is the case, I don't know of any way to change the order other than with the after_update event to set the Selection value to True.

[zmbd]

Try this...
Set the database to open in multiple panes instead of tabs.
Open "tblDeviceJob"
Open "tblDevice"
Open "qryDecviceSelection_AddingDevice"
Arrange all of these so that you can see the last few records in each of the tables.
For the following... pay attention to "tblDevice"
Now... in "qryDeviceSelection_AddingDevice"
New record
Notice that the checkbox is currently null
Select the checkbox and move to new record...
Did you notice what happened in "tblDevice" ?
Make a note of the last [DeviceNumber]
repeat...

Still in "qryDeviceSelection_AddingDevice" add a random job... say 3... the last device number 480 populates the field... you can change it... and the check box will change state to match the state as shown in tblDevice...

When we change the checkbox in the form to have no value as the default we're matching what happens in the query itself when you start a new record... but for some reason... in the form... when the control is set to a value when it starts a new record... instead of inserting new record in tblDevice as the query did... it's forcing the error.

Still looking into that; however, it may be a bug...

-z

[Seth Schrock]

Well, I'm not sure if this makes sense or not, but this is what I noticed. In tblDevice, DeviceNumber is an autonumber that is only up to 417. However, when I put in the JobID 3 in qryDeviceSelection_AddingDevice, the number that pops into the DeviceNumber is 434, for which I have no record in tblDevice. This would explain the error message of having not being able to add a value to the Many table without there being that record in the One table. My question is why is it putting 434 into that field? I think that if we can solve that, we have solved the problem. Could it be that it is adding records to tblDevice (like it was talking about in that article you shared with me), but not showing those records?

[zmbd]

I D/L a fresh copy of the BE from your post as I'd been playing with your DB.

Indeed the last value in [tbldevice]![devicenumber]= 417

Now I... I did the same thing as I posted earlier (#24)
The record added to tbldevice had [DeviceNumber]=432

I think if you D/L from your post into a clean test folder... relink D/L FE to D/L BE and do as I have done we'll be in sync there...

Why is "qryDeviceSelection_AddingDevice" allowing inconsistent updates to the tables by creating records on the one-side of the relationship?!

I checked the query properties (open in design mode, open property sheet, click in an empty area in the table design) and it clearly shows that the "recordset type" is Dynaset not "Dynaset (Inconsistent)" Thus, I would expect the referential integrety to be maintained at the query level...

Basically we should be seeing the same error with the query that we're seeing in the form! Instead... the query is inserting a new record into "tbldevice"

-z

[Seth Schrock]

I can't figure anything out. Admittedly, I'm way over my head at this point. I think that I just keep using my work-around. Thanks for all your help.

[zmbd]

Seth,
As I'm at a loss as to why the query is allowing inconsistent updates and the form is enforcing them when the chkbx.ctl is set to true, I've asked for another pair of eyes to look over what we've done to see if we've missed something or I've mis-understood something (more likely :) )
-z