VB to automate a procedure

[whoisit]

I would like to use some VB to automate a certain procedure. I am currently working on a movie rental database.

In the table tblTitleCopy I have the fields CopyId, TitleId, DatePurchased and Damaged

Below pic is example of some data in the table

As you can see there are 6 different CopyId for the same TitleId, 3 for Videos and 3 for Dvds. The D and V at the beginning of each CopyId means either Dvd or Video. At the end are the letters A, B, or C that shows which copy it is. So here is how the CopyId works, starting with D or V, followed by the TitleId, then the letter A, B, or C. I want to be able to click on a button, which will automatically create these 3 Dvd and 3 Video (total of 6) copies for me, and if these copies already exist then I want a message box to appear with some invalid message.

Below pic is what the form will look like

Any Suggestions on what I could do
If no solutions are possible then no prob
THANK YOU

 

[FoFa]

You need to tell it you have 3 DVD's and 3 Tapes. Once that is done it would be easy to create a function/sub to create the records for you, and to do checking if they exist already.
Question What if you have say 2 DVD's and decide to add 2 more, then they would exist but then you just want to ADD to what you have?

 

[whoisit]

Thanks for the reply
best suggestion i have had from anyone so thank you FoFa
but the problem is i am a complete beginner at VB so i have no idea where to start

Any extra copies they want to add will be done manually by typing in the copyId and other fields manually

Further help would be really appreciated
THANK YOU

 

[whoisit]

Urgent, can anyone please help
THANK YOU

 

[spacepro]

Whoisit,

If you are working on a movie rental database, why would you create copies on the fly. Surely you would have a table where all you stock is situated, which could be added to when you buy new copies and when the copies are rented you lookup it see if it is available and if so then set a condition on that entry when it is rented out.

Can you explain why you would want to create copies on the fly by asking for the above. I need to understand what your aim is and how your project works before anyone can help you.

Regards

Andy

 

[FoFa]

Basically it would be something like this:

Sub CreateCopies()
Dim DVDcnt as integer, TAPEcnt as integer, LP as integer, strSQL As String
Dim CopyChar as int, TitleID as string
Dim db As New ADODB.Connection
CopyChar = 64 ' Set to one less than ANSI char A (if this goes past 90 your our of Letters)
Set db = CurrentProject.Connection

DVDcnt = Forms!MyForm![DVDcount] ' assumes there is a field on your form called DVDcount where they put in this count
TAPEcnt = Forms!MyForm![TAPEcount] ' Same as above only for tapes
TitleID = trim(Forms!MyForm![TitleID])

If DVDcnt > 0 then
  for LP = 1 to DVDcnt
    strSQL = "INSERT INTO tblTitleCopy (CopyId, TitleId, DatePurchased) "
    strSQL = strSQL & "Values('" & "D" & TitleID & chr(CopyChar+LP) & "',"
    strSQL = strSQL & "'" & TitleID & "',#" & Format(now(), "Short Time") & "#)"
    db.Execute (strSQL)
  Next LP
end if
If TAPEcnt > 0 then
  for LP = 1 to TAPEcnt
    strSQL = "INSERT INTO tblTitleCopy (CopyId, TitleId, DatePurchased) "
    strSQL = strSQL & "Values('" & "V" & TitleID & chr(CopyChar+LP) & "',"
    strSQL = strSQL & "'" & TitleID & "',#" & Format(now(), "Short Time") & "#)"
    db.Execute (strSQL)
  Next LP
end if

Set db = nothing
end sub

This most likely has errors in it, but should give you a start.

 

[whoisit]

Thank you very much
Looks very complicated
really appreicate thisso for example i say i want to create three DVDs using the titleId 01045
Will it automatically create these copies with date purchased as today's date and copyId in the form of D01045A, D01045B, D01045C etc.
THANK YOU
again really appreciate the help

 

[Pat Hartman]

You seem to be fixated on generating an "intelligent" primary key. That is poor practice and will eventually lead to problems. You really have a 1-many relationship that you are atempting to flatten by using this composite key. The 1-side table needs to contain the title, release date and other things related to the movie. The many-side table identifies the physical media copies of the movie. This table contains the MovieID which is the foreign key to the 1-side table and the media type. It also contains other information specific to this physical copy such as inServiceDate. The primary key of this table should be an autonumber.

tblMovie
MovieID (autonumber primary key)
Title
ReleaseDate
etc.

tblCopy
CopyID (autonumber primary key)
MovieID (foreign key to tblMovie)
MediaType
etc.

Using this structure there is NO need for any code. You simply need to define your relationships properly and select the enforce referential integrity and cascade delete options.

Do some reading about database normalization and table design. Using a single field to hold multiple pieces of information (media type, ID, sequence code) violates first normal form which says that each field should be atomic. In this sense Atomic means cannot be divided further and still retain some meaning.

Once you restructure your tables, you can use code similar to what was posted to insert the necessary rows in tblCopy.