Making a New Record

[Hayvek]

i have three forms which i want to collect from lots of data from. the four sources are:

[forms]![frmLoanmember]![MemberID]
[forms]![frmLoanCD]![CDNumber]
[forms]![frmLoan]![ReturnByDate]
[forms]![frmLoan]![LoanDate]

thats in my criteria. all the data needs to be taken to tblLoans in the field of the same name as the source field.

i need to collect the data from there and put into a table in one record. the thing is, is that the append queries work if i have four different queries but the data is inputted into four different records leaving spaces in all of the other fields in the records. i tried combining the queries into one but now its filtering them all out of the data is going to append out. now ive changed my criteria to OR and its still filtering out of the data and coming back with a message saying 'no rows with be appended' or something. i was thinking of a form that runs in the background maybe? im not sure what to do.

any help appreciated. thanks

 

[shay]

Don't think the Append query approach is correct. Try this insteaad:

Sub AddNewRecord()

Dim MyRst As Recordset

Set MyRst = CurrentDb.OpenRecordset("tblLoans", dbOpenDynaset)

With MyRst
.AddNew
!MemberID = Forms!frmLoanMember!MemberID
!CDNumber = Forms!frmLoanCD!CDNumber
!ReturnByDate = Forms!frmLoan!ReturnByDate
!LoanDate = Forms!frmLoan!LoanDate
.Update
End With

MyRst.Close

End Sub

shay

 

[Pat Hartman]

There is a basic concept problem here. Data is NOT stored in forms, it is stored in tables. Look at the recordsources of those forms and create a query that joins them. You should NOT need to create yet another table.

 

[Hayvek]

basically my projects a video rental shop with CDs. you have have details on members and cds in two tables. loans of CDs that are yet to be returned are in the tblLoans and when they are returned they are moved into the archive. the fines in tblfines are the outstanding ones and the fines that have been paid are in the archive

the idea is that i want to be able to select a member from a form using a filter form/drop-down menus etc go onto the next form. select a CD and how long the loan is and it'll make a record in tblLoans

 

[Rich]

You need to re-think you db design, you do not need to move items between tables to see items that have / have not been returned.
You have a one to many relationship between customers and loan items, a simple DateReturned field will alow a query to display items on loan or not as the case may be.
Search here for posts on normalisation (normalization), in partticular those articles written by Pat Hartman