Data Access Page -- multiple tables

[taylorantone]

I want to create a data access page that assigns a borrower to a borrowed DVD. On this page, I only want a field showing the DVD title and a drop down box defaulting to the currently assigned borrower, but allowing me to re-assign it.

The tables/queries are set up as follows (the two tables are obviously related).
DVD table--
title
borrowerID

Borrower table--
borrowerID
borrower

DVDborrower query--
title (from DVD table)
borrower (from borrower table)

No matter how I try to set up the access page--either using the tables or the query--if it DOES allow me to edit the borrower, it replaces the borrower in the entire table and not just the selected record.

What I thought needed to do was to pull the title from the DVD table in a text box, then use a drop-down box to be bound to the borrowerID in the DVD table but display the actual borrower's name using the borrower table, and then update the borrowerID in the DVD table when I make a different selection from the drop-down.

I can do this really easily in Forms/Queries, but the data access pages are a totally different animal apparently.

Thanks in advance!!!

 

[lespaul]

I would expect:

tblDVD
DVD_ID (Autonumber PK)
DVD_Title
DVD_ReleaseYear
(anything else about the DVD)

tblBorrowers
BorrowerID (Autonumber PK)
Borrower_FName
Borrower_LName
(anything else about the Borrower)

tblDVDBorrowed
BorrowedID (Autonumber PK)
DVD_ID (Long - FK to DVD_ID)
BorrowerID (Long - FK to BorrowerID)

That way you will enter a new record each time a DVD is borrowed.

HTH

Leslie

Check out 'The Fundamentals of Relational Database Design'

 

[taylorantone]

Yes, that's how it's set up.

tblDVD
dvdID (autonumber)
title
borrowerID
...other fields

tblBorrower
borrowerID (autonumber)
borrower

I don't need to track any past history. I just need some way to set up a data access page that allows me to pull up a DVD title and choose a new borrower.

I only created the query to see it was trying to pull in two tables manually that was causing the incorrect updates to the tables.

Thanks!

 

[lespaul]

No, it's not the same setup, you have borrowerID in the DVD table as well as in the DVD borrower table. You need to remove it from the DVD table.

You may also want a checkout date and return date in the DVDBorrowed table.



Leslie

 

[taylorantone]

Okay. But how does that get me to where I need to be with the data access page? Sorry if I'm not getting that part. I see what you're saying about the 3rd table...it's basically a way to get a many-to-many out of that relationship.

 

[lespaul]

If you change the borrower, then insert a new record with the DVDID and the new borrower ID. You don't need to change any records, just add a new record.




Leslie

 

[taylorantone]

Seems more complicated than I need it though. I just want the borrowerID in the tblDVD to change from a data access page, but I want to be able to use a drop-down box populated with the actual borrowers name to change it.

I choose the DVD record, then I drop-down the box and choose the new borrower from the list and save. That save puts the corresponding borrowerID into the borrowerID field of tblDVD.

Is the way you're suggesting the only way I can get that to happen?

 

[lespaul]

no, but if you are going to have BorrowerID in tblDVD:

tblBorrower
BorrowerID
Name
etc

tblDVD
DVD_ID
BorrowerID

then you don't need the other table at all. All you will do is a simple update statement of BorrowerID in tblDVD.

Leslie

 

[taylorantone]

That's how it's already set up. The update statement is exactly what I'm trying to do.

I just can't get it to work on a data access page, which takes me back to my original question.

 

[lespaul]

What update statement are you currently trying to use and how?

Leslie

 

[taylorantone]

I'm not. That's the thing I'm totally lost on.

I just need a data access page that allows me to change the borrowerID in tblDVD, BUT uses a drop-down box that is populated by the borrowers field in tblBorrowers so that I don't have to remember the IDs.

 

[lespaul]

OK, I haven't done much with Data Access Pages. In fact, most of the DAP questions go un-answered because it appears that ASP is actually easier. To accomplish this in an Access Form you would have a drop down list on the form who's source is a query:

SELECT BorrowerID, FName & " " & LName as FullName FROM tblBorrower

then you would have to have to set it up to show the FullName in the box and store the ID for later use (the wizard usually sets all this up for you.

that combobox would have a name cboFullName and in the afterUpdate (or an event named something similar) you would have an update statement:

UPDATE tblDVD set BorrowerID = forms!FormName!ComboBoxName WHERE DVD_ID = whatever DVD you are trying to reassign.

You may be able to take a working form and change it to a DAP, I don't know.

Sorry I couldn't help more, but your initial table design threw me off.

Leslie