Form creates unwanted rows in table

I've created an asset database which keeps track of computers, laptops
etc.  There is one company which has many departments and every
department has many rooms. (surprise, surprise)

There are four tables: tables Department and Asset and two "middle"
tables DepartmentRoom and AssetRoom.
The table structures are as follows:
DEPARTMENT
id
name
dep_type
dep_num
etc...

ASSET
id
name
a_type
ip_num
vendorID
serial_num
etc...

DEPARTMENTROOM
id
name
depID (foreign key to department)

ASSETROOM
id
assetID (foreign key to asset)
roomID (foreign key to departmentroom)

Relationships are:
DEPARTMENT.id  (1-many)  DEPARTMENTROOM.depID
DEPARTMENTROOM.id  (1-many)  ASSETROOM.id
ASSET.id  (1-many)  ASSETROOM.assetID

I've created two forms: AssetForm and DepForm. There are subform in
both those forms. AssetFormSubform is used to pick department and room
for the current asset (there are two comboboxes). The source of
depCombo is DEPARTMENTROOM.depID (there is a query to DEPARTMENT table
so I can see the names instead of ID numbers) and roomCombo
DEPARTMENTROOM.name so both are bounded.

Likewise there is a subform in DepForm to add rooms in current
department.

Now the problem is that when I try to select department for current
asset (in AssetFormSubform) it creates new rows in DEPARTMENTROOM
table? I only want to select the existing department and room. AND
ONLY ASSETROOM SHOULD BE MODIFIED. The ASSETROOM table works (it fills
up automatically).

Now what? Is table design incorrect or what? shoud I use unbound
combos instead of bound ones?
0
Sandroid
2/15/2010 3:28:35 PM
access.forms 6864 articles. 2 followers. Follow

1 Replies
807 Views

Similar Articles

[PageSpeed] 38

The asset main form would be based on a query using the asset table.
The subform where you choose Asset and Room, would be based on a query using 
the AssetRoom table.
The link master and link child fields would use the field that has the Asset 
ID.
You would put one combo to choose the room, as you have already chosen an 
asset from the parent form.
You thus don't need an additional combo to choose an asset.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Sandroid" <santeri.virtanen@gmail.com> wrote in message 
news:7460e851-2eef-4078-89f6-74b48edea4a1@v25g2000yqk.googlegroups.com...
> I've created an asset database which keeps track of computers, laptops
> etc.  There is one company which has many departments and every
> department has many rooms. (surprise, surprise)
>
> There are four tables: tables Department and Asset and two "middle"
> tables DepartmentRoom and AssetRoom.
> The table structures are as follows:
> DEPARTMENT
> id
> name
> dep_type
> dep_num
> etc...
>
> ASSET
> id
> name
> a_type
> ip_num
> vendorID
> serial_num
> etc...
>
> DEPARTMENTROOM
> id
> name
> depID (foreign key to department)
>
> ASSETROOM
> id
> assetID (foreign key to asset)
> roomID (foreign key to departmentroom)
>
> Relationships are:
> DEPARTMENT.id  (1-many)  DEPARTMENTROOM.depID
> DEPARTMENTROOM.id  (1-many)  ASSETROOM.id
> ASSET.id  (1-many)  ASSETROOM.assetID
>
> I've created two forms: AssetForm and DepForm. There are subform in
> both those forms. AssetFormSubform is used to pick department and room
> for the current asset (there are two comboboxes). The source of
> depCombo is DEPARTMENTROOM.depID (there is a query to DEPARTMENT table
> so I can see the names instead of ID numbers) and roomCombo
> DEPARTMENTROOM.name so both are bounded.
>
> Likewise there is a subform in DepForm to add rooms in current
> department.
>
> Now the problem is that when I try to select department for current
> asset (in AssetFormSubform) it creates new rows in DEPARTMENTROOM
> table? I only want to select the existing department and room. AND
> ONLY ASSETROOM SHOULD BE MODIFIED. The ASSETROOM table works (it fills
> up automatically).
>
> Now what? Is table design incorrect or what? shoud I use unbound
> combos instead of bound ones? 


0
Jeanette
2/15/2010 8:10:43 PM
Reply:

Similar Artilces:

FORM funtion 2003/2007 versions
in 2003 version there is the FORM funtion(under data heading) that speeds up the population of small data bases by creating an "input" sheet.I am trying to locate the same funtion in the 2007 version. Sergio It's not on the ribbon, you can only add it to the QAT (Quick Access Toolbar) This is the small row of icons by default are top left, by the Office Button. To add this, right click on the QAT and select 'Customize Quick Access Toolbar...' Select 'Command Not in the Ribbon' from the 'Choose commands from' dropdown In the listbox below select ...

How to count rows with changing data
I have an imported list on sheet2 and it is maybe 100 rows. Each day the data is imported the dates change along with the type of record associated with each date. Say this week there are 25 rows with 11/13, 20 rows with 11/14 and 25 rows with 11/15 and 30 rows with 11/16. Mixed in with this each of these dates might have a different type of record (each type has 4 options.) I need to be able to do the following: 1.) I need to count the number of occurrences for each date and not only show the total count but also show the date that is counted as the label. 2.)count the...

Query Tied to Form doesn't work
I have a button on a form that populates a text box on the same form with text. I have a query where a field's criteria is a reference to the text box on the form so the text in the text box becomes the criteria for the query. After I click the button to populate the text box on the form and then run the query, it comes up blank (should be records). If I click the button on the form and copy the text from the text box to the query (in stead of a reference to the text box on the form) the query works fine. Any ideas why the query won't work with the reference to the text box on...

How to use Like and Or in Form.Filter VB Statement
This Works: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" FilterOn = True This Doesn't: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" Or "[Project Name] Like '*" & Forms![Master Search]!Text64 & "*'" FilterOn = True what is the correct syntax How many times are you going to ask the same question? I've already answered you: did that not work for you? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) <himmelrich@gmail.com...

Displaying report filters on Excel Pivot Tables
I am using pivot charts to display different departments' data, over the course of the year. When looking at the graph, I often filter it to see just one department. When viewing and printing, I would like to be able to see which department the chart is filtered on. Is there a way to do this? ...

email created in publisher has jibberish
When I create an email in Publisher and send it to my smtp email address, I open it up to read the email and it has the following jibberish before the actual email: v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} b\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Is this something that can be prevented in the creation? Or is this something that happens after it is emailed? ...

Creating a frame window for MDI in a DLL
Have a question on creating an MDI 'application' from a DLL. I would like to launch an MDI 'psuedo' application from a DLL. That is, when this method is called from the main application, it would launch what would look like another application, that is an MDI frame with doc/view, etc.. I have seen something like this in .NET forms, however, I am using unmanged C++ code. I have some experimental code work done now and it appears that the call to LoadFrame fails (yes, I have included all of the necessary IDR_MAINFRAME resources). Is this possible? Has anyone done this? An...

Pivot table add one column
How can I add one column in pivot table? When I added a column of Average 100, pivot table created 3 more columns for the first 3 columns( and I don't like it) My data Book Label Price Average A Hard paper 200 100 B Soft 100 100 C Hard paper 200 100 F Plastic 50 100 K Soft 100 100 L Plastic 50 100 M Hard paper 200 100 K soft 100 100 R Soft 100 100 T Plastic 50 100 ------------------------------------------------------------------------------------------- My pivot ...

show items with no data option in pivot tables
Can anyone help with some bizarre results I'm experiencing with the show items with no values check box in the field settings menu for pivot tables. It appears to be showing field headers that don't exist in my data???? I'm using Excel 2000. Debra Dalgleish has some techniques at: http://www.contextures.com/xlPivot04.html In fact, she has an addin that you may like: http://www.contextures.com/xlPivotAddIn02.html AHuntington wrote: > > Can anyone help with some bizarre results I'm experiencing with the show > items with no values check box in the field setting...

Exchange 2007
Hi there, I'm trying to create a public contact folder in Exchange 2007. I can create a new public folder using the "New-PublicFolder" shell command, but it defaults to Post item types. Could anyone tell me how to set it so that it defaults to Contact item types? Thanks, -Richard rk <rk@leapbeyond.com> wrote: > Hi there, > > I'm trying to create a public contact folder in Exchange 2007. > > I can create a new public folder using the "New-PublicFolder" shell > command, but it defaults to Post item types. Could anyone tell me how > t...

Create Organization failed
I've moved my deployment to the production server and now want to create a new organization for testing purposes on the developmente server. When I run the New Organization wizard i get this error: Error| Create new Organization (Name=BigSky, Id=3a238cdc-773f-de11-81d3-000d9d4d28a8) failed with Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.DirectoryServices.DirectoryServicesCOMException (0x80072037): There is a naming violation. (Exception from HRESULT: 0x80072037) I have had the same problem and sam...

Were can I find a design master I created?
I created a master slide design and it does not show up in the design template menu. Where could it be found? Julio Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "Julio" <Julio@discussions.microsoft.com> wrote in message news:E246AB3D-9D8E-4C3B-A513-3E12EC97571E@microsoft.com... >I created a master slide design and it does not show up i...

Conditional Text in textbox control of continuous form
Hello, I read a lot of things about conditional formating (color, font...) but how can I make a condition like: if text="ST" then text="PC" So I just want to show other text in the specific textboxes in the continuous form in the case of condition. Is this possible? juvi Conditional formatting won't change the contents of the textbox. That's not formatting, that's data. You can do this with vba however. A practical application would be if the user chooses a particular product in a form and you want to put the price that is normally charged for this product...

A form problem
On my main form, Home Base, is a subform that shows me a list of persons. I have a set of command buttons that make different report for the selected person. That works fine. What I need to do is a command button that modifies the list, Ex : 30 days, 60 days 90 days. I click the 30 days button and the list will be composed of the people that I saw in the last 30 days. I can make a query and all for that but I can't manage to simply update (or requery) the list with the selected filter (or query) without changing the hole screen. How can I update the present subform wit...

Form with cells and TextBoxes...how to TAB to textboxes?
I have a simple sheet that's being used as a questionnaire form. The sheet is Protected, only allowing users to select unlocked cells. Users tab from cell to cell and either type in or select an item from a list (Data/Validation). I also put in some textboxes but I can't tab into them. Is there a way to get the tabbing to work? Thanks, Toby Erkson Oregon, USA WindowsXP, Excel 2003 AFAIK, you cannot tab to a textbox. Tabbing only works between cells. Why would you want to tab to a textbox anyway? "Toby Erkson" <not@necessary.com> wrote in message news:us#QMv9qE...

Help Creating A Rule
I need help creating a particular rule. I get many SPAM emails that contain no text at all. Instead it is a graphic type that displays text. It looks like "Plain Text" but it is really a photo embedded into an email. How can I write a rule that will prevent this type of SPAM. You can't search the body for text. It is worthless trying to write something that can spot the issue in the header because it can vary so much. Any way to write a rule that can detect the absent of all text? Don't use rules - set your Outlook Junk Filter to highest. --� Milly Staples [MVP - Ou...

Can a form linked to an autoLookup query update another table
I created a form that is updated with an AutoLookup query from another table once a certain linked field is filled in. Can this form be used to add a record to the source table with the updated information? When that record is saved, the record is added to the form's source table - assuming the form is bound to the source table. How does your form save the record? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "oterosuz" <oterosuz@discussions.microsoft.com> wrote in message news:3608D905-8769-435E-B5E5-7B0F566AFD63@microsoft.com......

Need 2 rows to display X axis data points for a line graph
X axis is 100 data points, and all must be displayed. They do display, but are all mushed up. In Corel, you have the option to display the X axis on up to 3 rows, with interspersed tick marks. In Excel, I seem to be able only to manipulate the number of categories between tick marks and tick mark labels, but can't stagger data onto multiple rows. Any ideas or add-ins I could try? Excel 2002. Thanks, Susan This web page gives a suggestion for staggering your labels: http://peltiertech.com/Excel/Charts/Staggered.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technic...

I have 2 Access DB with linked tables
In DB 1 is frmTokenDelivery and in DB 2 are tblImageBuild and tblHardwareRequest. I link to the tables from DB1. The DBs belongs to two seperate departments. The two departments perform seperate operations that must be completed before hardware is delivered to the user or to another dept. for further action. My Need: I want to display a date in DB 2 on frmTokenDelivery in DB 1. Field in DB 2 is ProDate (i.e projected dated). Sorry for the long explaination. Any assistance will be highly appreciated! With regards, Sokan33 wrote: > In DB 1 is frmTokenDelivery and in DB 2 are tblIm...

REPLY TO: how can i creat options in a cell of excel worksheet
You really need to give more information on what you're looking for. What do you mean by "Options"? Also, if you don't leave a message in the message portion when you're posting, many of us can't reply to the request. HTH, Barb Reinhardt ...

Problem Opening Forms Assistant By Default For Custom Objects
For standard CRM objects such as the Account, it's possible to open the forms assistant by default by setting relatedInformationCollapsed= “false” for the main form in the customization file. This same solution does not appear to work for custom objects and I am wondering if anyone else has encountered this problem and/or has a solution. Thanks, Darryl ...

W-2 Forms
My accounting head tells me she read "somewhere" that we won't be able to print W-2s for 2007 on GP 9. We will have to upgrade our hardware for 10 and I am not quite ready for that. Can anyone set me straight on this? Thanks!!! Haven't heard ANYTHING of the sort. Now, Microsoft is no longer supporting (after August that is) version 8 and any W2 changes could be problems there. But V9 is not expected to be a problem. -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at h...

calculated column in pivot table
Can I create a calculated column in a pivot table from two other columns. I have tried creating formulas but that does not seem to do the trick. The answer is yes. For anything less general, we'll need specifics on what you tried, and what happened. "does not seem to do the trick" does not give us much to go on. Regards, Fred "freeriderxlt" <st.jdaich@gmail.com> wrote in message news:97a60a38-262d-4a91-9474-7c26d8c44be3@a16g2000pre.googlegroups.com... > Can I create a calculated column in a pivot table from two other > columns. I have trie...

Display Data Table in chart but don't show plot area
I am using Excel 2003. I have created some charts/graphs with data tables, chart titles, etc. My customer only wants to see the data table and chart title for each graph. Is there a way to turn off the plot area and change the size of the chart so that the chart title is close to the data table? But the data table merely reflects what is in the cells used to make the chart! Why not just display that data in a new format? Or am I missing something? best wished -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "RW" <RW@discussi...

Appending the rows of several tabs in xls sheets?
Is it possible to append all the rows of several tabs into one sheet? We are getting inventories in excel files organized in several tabs: 1st Tab: instructions for the users 2nd Tab: information about the country (currency, tax rate, etc) 3rd Tab: inventory of one site within the country ....to... All tabs have the same format (5 rows header, then identical Nth Tab: rows containing data about one item per row. The name of each tab 3 to N is the site name (company code). N is of course different for each country, and the number of lines in each site inventory is also variable (but they a...