TRANSFER INFO TO ANOTHER TABLE ON A FORM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to transfer infomation from a form to another table other than
the one I am using whenever the user check in a box. I need some help on
tell me how I can do this. It seems everything I try I get and error. Can
someone tell me how I can do this????
 
Typically, this is not a good idea. What are you trying to accomplish here?
Sounds like you might be trying to move records from one table to a
"historical" table? If you are checking a box in a record, there is
probably no reason to move it to another table. You can use that checkbox
to filer your forms, queries, and reports to include or exclude the records
with checks.

Post back more details if you need further help.
 
You have guest what I am trying to do. I trying to keep certain data from
one table and moving it to another and then delete the information from the
main table. I am trying to use the check box to make all this happen at one
time. It works find on deleting the information, but I can not figure out
how to make the information go to the other table before the delete. Can you
help?????
 
Again, you should not do that. Just call your checkbox "archived" or
"inactive". Alter your "active" queries, forms, and reports to exclude any
records where the box is checked. You can then create reports, queries, and
forms where you can see only archived data, only current data, or both
combined data.

The normal recommendation here is to keep all the data in one table and mark
them as archived.
 
I understand what you are talking about. However, the information in the
orginal table is going to be use over and over again. The only thing I want
to move is a title and two dates. This is a library and the book information
will not change. I just want to send the title of the book and the date
checked out and the current date (date checked in) to the other table.
 
AHHHH.

You have a one-to-many relationship with your books vs. transactions. You
should have at least three tables here. (none of which would have the same
fields, but different levels of data - inactive vs. active, history vs.
current, etc.) Each table will store completely different types of data and
will have totally different fields. In a normalized database, you don't
have multiple tables with pretty much the same fields.

One would contain all the book information. The other table would contain
the Dates that each book was checked out and in. You need to redesign and
normalize your tables.

TblBook
BookIDNumber
Title
Author
Summary
BookInactive (checkbox when a book is no longer available)
etc.


TblTransactions
BookID
Transaction (check in or out)
Date
CustomerNumber


TblCustomers
CustomerNumber
CustFirstName
CustLastName
CustAddress1
CustAddress2
CustCity
CustState
CustZIP
CustHomePhone
CustomerInactive (checkbox)
etc.


You would maintain the book information in one table. Checking a book out
or in would create a record in your transaction table.

If you are trying to track all of this in one table, then you are
"committing spreadsheet". Each table should contain one type of data only.
You are maintaining several types of data in one big table.

Notice that in the above, we have a way to inactivate a book or a customer.
This will allow you to still go back and view the history for those books or
customers, but you can set it up so that they will not be available in your
forms to perform transactions.

Post back you actual field names and definitions if you need help
"normalizing" your data.

Once you normalize, you can have a "customer" form with a subform showing
all the books they have checked out. You could have a "book" form showing
all the times it was checked out and by whom. You could have a list of all
the books checked out during a certain time frame. Lots of options. The
key is to build yourself a relational database first.
 
I do appreciate the information you provided me. I was able to talk to a
visual basic programer and he wrote the following that works which does what
I was trying to do.

Private Sub Check8_Click()
Dim libConn As ADODB.Connection
Dim rstLib As New ADODB.Recordset
Dim historySql As String

Set libConn = CurrentProject.Connection

rstLib.CursorType = adOpenDynamic
rstLib.LockType = adLockOptimistic

rstLib.Open "BOOKHISTORY", libConn, , , adCmdTable
With rstLib
.AddNew
!EMPLOYEE = Trim([CHECK OUT BY].Value)
!BOOKTITLE = [TITLE].Value
!DATECKOUT = [DATE CHECKED OUT].Value
!DATECKIN = Date
.Update
.Close
End With

Set rstLib = Nothing
MsgBox "Book Checked In!", vbInformation, "History"
 
We never said you couldn't do it, just that doing it that way is wrong (from
a normalized relational database point of view).

--
RBear3
..

jrp444 said:
I do appreciate the information you provided me. I was able to talk to a
visual basic programer and he wrote the following that works which does
what
I was trying to do.

Private Sub Check8_Click()
Dim libConn As ADODB.Connection
Dim rstLib As New ADODB.Recordset
Dim historySql As String

Set libConn = CurrentProject.Connection

rstLib.CursorType = adOpenDynamic
rstLib.LockType = adLockOptimistic

rstLib.Open "BOOKHISTORY", libConn, , , adCmdTable
With rstLib
.AddNew
!EMPLOYEE = Trim([CHECK OUT BY].Value)
!BOOKTITLE = [TITLE].Value
!DATECKOUT = [DATE CHECKED OUT].Value
!DATECKIN = Date
.Update
.Close
End With

Set rstLib = Nothing
MsgBox "Book Checked In!", vbInformation, "History"

RBear3 said:
AHHHH.

You have a one-to-many relationship with your books vs. transactions.
You
should have at least three tables here. (none of which would have the
same
fields, but different levels of data - inactive vs. active, history vs.
current, etc.) Each table will store completely different types of data
and
will have totally different fields. In a normalized database, you don't
have multiple tables with pretty much the same fields.

One would contain all the book information. The other table would
contain
the Dates that each book was checked out and in. You need to redesign
and
normalize your tables.

TblBook
BookIDNumber
Title
Author
Summary
BookInactive (checkbox when a book is no longer available)
etc.


TblTransactions
BookID
Transaction (check in or out)
Date
CustomerNumber


TblCustomers
CustomerNumber
CustFirstName
CustLastName
CustAddress1
CustAddress2
CustCity
CustState
CustZIP
CustHomePhone
CustomerInactive (checkbox)
etc.


You would maintain the book information in one table. Checking a book
out
or in would create a record in your transaction table.

If you are trying to track all of this in one table, then you are
"committing spreadsheet". Each table should contain one type of data
only.
You are maintaining several types of data in one big table.

Notice that in the above, we have a way to inactivate a book or a
customer.
This will allow you to still go back and view the history for those books
or
customers, but you can set it up so that they will not be available in
your
forms to perform transactions.

Post back you actual field names and definitions if you need help
"normalizing" your data.

Once you normalize, you can have a "customer" form with a subform showing
all the books they have checked out. You could have a "book" form
showing
all the times it was checked out and by whom. You could have a list of
all
the books checked out during a certain time frame. Lots of options. The
key is to build yourself a relational database first.

--
Hope that helps!

RBear3
..
Click to expand...
Click to expand...
 
Back
Top