forms - Integrate junction and lookup tables with a basic Access database -


i have simple database stores customer's name, city , state. i've added 2 new tables. 1 lookup table, describing car model, , second junction table setting 1 or more car models customer record.

here's relationship layout of now:

enter image description here

and datasheets each table. cust_id , model_id auto increment columns.

demographics

enter image description here

lookup_model

enter image description here

junction_model

enter image description here

using scripting language, mysql , html whip complete form in few minutes. having little trouble figuring out how done in access 2007.

two features need are

  1. ability edit/add items lookup table
  2. ability add new customer records including selecting cars found in lookup table, while maintaining primary key relationships.

ex:

"mary jane" owns ford pinto , datson 510. when add info to-be-created form, cust_id 5, , 2 new entries show in junction table cust_id: 2 (ford) , 5 (datsun).

is there relatively painless way of setting form in access 2007?

edit: i've managed working far. subform lists correct model/cust associations, looking show combo box model_desc shown (not model id).

enter image description here

a form containing "subform" serve in case this.

  • you can create form named "owners" bound [demographics] table in can add/edit owner information (name, address, ...).

  • you can create "continuous forms" form named "cars_owned_subform" bound [junction_model] table. form contain combo box row source [lookup_model] table, displaying [model_desc] , returning [model_id].

  • then can add subform control "owners" table using subform wizard , link subform parent based on [cust_id]. move 1 owner next cars automatically update in subform, , if add new car in subform automatically associated [cust_id] of owner record being displayed.

as editing lookup table, create simple form edit [lookup_model] table.

edit re: question update

to have combo box displays [model_desc] , returns [model_id] you'll want have combo box "display" 2 columns first column has width of zero. is, format properties of combo box should include values like...

column count: 2
column widths: 0";1.3646"

...and data properties should include values like...

row source: lookup_model
bound column: 1

the final result this:

owners_form.png

the subform in design view be:

subform.png


Comments

Post a Comment

Popular posts from this blog

c# - DetailsView in ASP.Net - How to add another column on the side/add a control in each row? -

i have detailsview control on page used edit various fields of record, works in respect. i looking way add 1 column (and if works, why not more) right, absolutely read-only, show same fields of record comparison purposes. i aware there no obvious way such thing out of box detailsview . have looked other controls (transposed gridview , recommended formview , listview ), nothing satisfies. have special data binding setup using detailsview , can't out of without losing features. anyone on how "hack in" additional columns (for display only) on detailsview ? the solution have now, use second detailsview , visible set false in aspx. in code, make sure databind hidden detailsview hosts data third column first, initial detailsview named itemdetails . and in item created event, pass third column html rendering of hidden controls (in last code block) : protected void itemdetails_itemcreated(object sender, eventargs e) { if (dataite
Read more

javascript - firefox memory leak -

i have following script (i use raphaeljs also) draws circle, waits 1 second, draws circle again, etc (you can copy paste - functional, need include raphael raphaeljs.com): <div id="holder"> </div> <script src="raphael.js" type="text/javascript"></script> <script> var paper = raphael(10, 50, 320, 200); var rad = math.pi / 180; var finangle = 0; function sector(cx, cy, r, startangle, endangle, params) { var x1 = cx + r * math.cos(-startangle * rad), x2 = cx + r * math.cos(-endangle * rad), y1 = cy + r * math.sin(-startangle * rad), y2 = cy + r * math.sin(-endangle * rad); return ["m", cx, cy, "l", x1, y1, "a", r, r, 0, +(endangle - startangle > 180), 0, x2, y2]; } var path = sector(100,100,50,0,180); var arc = paper.path(path); function draw() { path = sector(100,100,50,0,finangle);
Read more

Trying to import CSV file to a SQL Server database using asp.net and c# - can't find what I'm missing -

i student , quite new programming, , given task using asp.net , c#, without being taught either. plan learn teach ourselves. the task stuck on making website import csv file sql server database of last year's room bookings around campus. the table has following columns: request_id; priority; module_id; day; start_time; length; park; students; room_code; status; semester_id; linked_request; week_1; week_2; week_3; week_4; week_5; week_6; week_7; week_8; week_9; week_10; week_11; week_12; week_13; week_14; week_15; ) in code, try make work 3 columns (request_id,priority,module) starters. when press import button, want read .csv file (with fixed name @ fixed directory) eg "1,2,3" - comma separating fields [delimiter] - , import sql server datab
Read more