Use form to edit records in 2 seperate tables

[NightSpy2]

Hey guys!

I've got a problem which I can't seem to figure out the code for. I have the logical process, but I just don't know how to put it into code.
I have a form with two boxes HireMovieID and HireCustomerID, with a button HireButton.
The two tables which I have are CustomerInfo and MovieList.
Here's the psuedocode for what I need to happen:

IF HireMovieID.Genre = "R16"
    (IF DOB > Today - 16 years)
         Display Error: "Too young"
         ELSE Hire()
ELSE IF HireMovieID.Genre = "R18"
           (IF DOB > Today - 18 years)
                Display Error: "Too young"
                ELSE Hire()
ELSE Hire()
Hire() = Add MovieID to a column 'Customer Hire History' for that CustomerID
           SET MovieID's LastHireDate to Today
           SET CustomerID's LastHireDate to Today
           SET MovieID's status to the CustomerID
           Add 1 to CheckNum for that HireMovieID

Oh yea and I need to somehow make it so that the MovieID is valid, as in, if it doesn't exist then it comes up with an Error MsgBox
I've tried a few basic ways but I can't really seem to figure out how to do it! :banghead:
If you could help that'd be great! I'm sure it's probably all easy to you guys, so if you would be so kind as to explain what you're doing/why you're doing it so that I could learn, that'd be great!

 

[MarkK]

You need more tables. Movie and Customer are not enough. Then, rather than write a bunch of code to set various LastHireDate values, you simply add a record, say an Order, that has a field called HireDate. That table might look like . . .

tOrder
OrderID (Primary Key)
CustomerID (Foreign Key)
HireDate
HireTotalAmount

tOrderDetail
OrderDetailID (PK)
OrderID (FK)
MovieID (FK)
ReturnDate
Price

... so your movies are connected to your customers through these new tables. And presumably you'll have orders that you need to keep track of, maybe you want to call them Hires???

And then LastHireDate for a customer is determined by checking the actual orders...

DMax("HireDate", "tOrder", "CustomerID = " & this.customerid)

hth

 

[NightSpy2]

Oh ok. I've got a table Hire History, and the data in it is laid out like this:

Is it possible to make it so that when (for example) John Smith hires something, it adds another cell to the end of his hires in this sort of format?

 

[MarkK]

That's not how a database works. A database doesn't have cells. When you add data to a database you always add rows. And how does the BirthDate of a customer belong in a table called HireHistory?

Maybe you want to read up on 'database normalization,' which is a topic that discusses how to store data in database tables. If you design you data structures in violation of certain principles they become very very difficult to work with.

Best of luck,

 

[NightSpy2]

I forgot to delete it because I was copying my data from my CustomerInfo table.

Ok thanks!