473,899 Members | 4,542 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Why can't I add records through my query?

Seth Schrock
2,965 Recognized Expert Specialist
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblDeviceJob.DeviceNumber,
  2. tblDeviceJob.JobID,
  3. tblDevice.Selection
  4. 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.Selec tion 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?
Aug 10 '12
27 5322
Seth Schrock
2,965 Recognized Expert Specialist
I got it to work with the following in the After_Update event for DeviceNumber:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub DeviceNumber_AfterUpdate()
  5. If Me.Dirty = True Then Me.Dirty = False
  6. If Not IsNull(Me.DeviceNumber) Then
  7.     Me.Selection = True
  8. End If
  9.  
  10. 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.
Aug 13 '12 #21
zmbd
5,501 Recognized Expert Moderator Expert
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
Aug 13 '12 #22
Seth Schrock
2,965 Recognized Expert Specialist
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.
Aug 13 '12 #23
zmbd
5,501 Recognized Expert Moderator Expert
Try this...
Set the database to open in multiple panes instead of tabs.
Open "tblDeviceJ ob"
Open "tblDevice"
Open "qryDecviceSele ction_AddingDev ice"
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 "qryDeviceSelec tion_AddingDevi ce"
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 "qryDeviceSelec tion_AddingDevi ce" 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
Aug 13 '12 #24
Seth Schrock
2,965 Recognized Expert Specialist
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 qryDeviceSelect ion_AddingDevic e, 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?
Aug 14 '12 #25
zmbd
5,501 Recognized Expert Moderator Expert
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 "qryDeviceSelec tion_AddingDevi ce" 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
Aug 14 '12 #26
Seth Schrock
2,965 Recognized Expert Specialist
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.
Aug 15 '12 #27
zmbd
5,501 Recognized Expert Moderator Expert
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
Aug 15 '12 #28

Sign in to post your reply or Sign up for a free account.

Similar topics

4
4824
by: news-server.tampabay.rr.com | last post by:
Hi, Below is a stock script I found which controls a framed environment. My problem is that if a URL has a query string attached, that string does not pass through. Can someone please let me know if and hopefully how, it will be possible to carry a query string through? This first part here is in the default.asp framed page. <SCRIPT LANGUAGE="JavaScript">
2
2788
by: Jason | last post by:
I have a table of 650,000 records associated to contracts. Some of the contracts have multiple records but each contract only has one Active Record (there might be several inactive records). There are dates associated with each of the records (whether active or inactive). I need to compare the dates between the active and inactive contract records. So far, I've created a "find duplicates query" for contract to identify contracts that...
4
2210
by: redryderridesagain | last post by:
My macro is writing to a table(T) and reading a query (Q) based on that table (VBA/Visual Basic 6.3). I cannot write T and read Q in the same execution of the macro, however, if I skip the writing part I can read Q just fine. How does closing the macro reset the state of the system? The SQL for Q is-- SELECT Relatesto.contains, Relatesto.iscontained FROM Relatesto GROUP BY Relatesto.contains, Relatesto.iscontained;
9
18590
by: Mike Bridge | last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me the error "Join expression not supported". Apparently, Access "fixed" it for me. Unfortunately, I can't correct it because when I click "OK", the window disappears. I don't see any way to open the query directly in SQL view, and both the Design and Data views exit as soon as I close the error dialog. Is there any way...
4
7778
by: alltechsolutions.net | last post by:
Been at this for two days now... I have a customer database with various fields, two of which are: ContactID (an autonumber field) & ReferralID (long Integer) When a customer refers someone new, the new customer gets the referee's ContactID in their ReferralID field. So someone who refers 10 people will have 10 instances of their ContactID scattered down the ReferralID column.
0
964
by: Paul M | last post by:
Hi I'm using c# for a web site app. In dataset I created tabledata adapter, but I need specific sql terms like PLAN for index. How can I deactivate query parsing when is generating select sql? TIA, Paul
11
2425
by: cgrider | last post by:
I have a query that checks for duplicate records on 2 fields. the issue I am having is that the data unfortunately is case sensitive. So M != m but the query is case insensitive. Is there a way to make my query case sensitive so I can cancel out the False positives from the query? here is the quey if that helps SELECT tblPreReleaseData.From_Conn, tblPreReleaseData.From_term, tblPreReleaseData.Serial_number,...
0
1197
by: sara | last post by:
Hi - I posted this earlier and didn't get any response. Hopefully someone can help?? I have a table keeping track of employees' jobs in a location. So, Store #1 Manager #353, AsstMgr #556 Store #2 Manager #776, AsstMgr #132 etc.
20
2249
by: raddrummer | last post by:
Hi there, I'm woking on a function that takes the input from a form (including Payroll Contact), uses it as a query parameter, runs the query, and then emaills out a custom .xls file using the sendObject method to the Payroll Contact specified in a list. After perfroming this function it loops to the next person on the list and requeries to send them a custom list. The problem: After sending the first list the first query will not...
1
1812
by: yfangl09 | last post by:
I have one query with a list of people and required courses they have to take and another with the same people and courses they have already taken. How do I generate a query with required courses they have NOT taken? Anybody have any suggestions?
0
10863
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10971
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10494
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4300
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3317
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.