473,883 Members | 1,852 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 5321
zmbd
5,501 Recognized Expert Moderator Expert
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
Aug 10 '12 #11
zmbd
5,501 Recognized Expert Moderator Expert
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
Aug 11 '12 #12
Seth Schrock
2,965 Recognized Expert Specialist
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
frmIPAddressMan ager
frmWarrantyRepo rt
sfrmDeviceSearc h
sfrmIPAddress
sfrmMonitor
sfrmWarrantyRep ort


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.
Aug 11 '12 #13
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Aug 11 '12 #14
Seth Schrock
2,965 Recognized Expert Specialist
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.
Aug 11 '12 #15
zmbd
5,501 Recognized Expert Moderator Expert
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.
Attached Files
File Type: zip Seth_InventoryDB.zip (641.9 KB, 83 views)
Aug 11 '12 #16
zmbd
5,501 Recognized Expert Moderator Expert
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.
Aug 11 '12 #17
Seth Schrock
2,965 Recognized Expert Specialist
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.
Aug 13 '12 #18
zmbd
5,501 Recognized Expert Moderator Expert
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
Aug 13 '12 #19
Seth Schrock
2,965 Recognized Expert Specialist
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.
Aug 13 '12 #20

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

Similar topics

4
4821
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
18588
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
963
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
2422
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
1196
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
2248
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
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9796
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10753
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...
0
10420
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...
1
7974
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7134
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
5804
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...
1
4620
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
3
3239
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.