Question Add and update multiple records to different tables

[silversun]

I have been looking for a solution in this site but there was no success. :banghead:
I have a pretty normalized Access Database but you can take a look at it (open the attachment) and if it was not normalized properly let me know corrections.
The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.
When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.
If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.
Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.
I have tried to approach this idea and spent a lot of time writing different VBA's and each solution had a problem.
For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.
Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.
By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.
Is my scenario clear enough? Please let me know if more details are needed.

 

[DavidAtWork]

Sounds like you need to reconsider your database design to handle returns in a more effective way. I can't open your database as I don't have 2007 at work, but because you can have many cards to one invoice and not all cards might be returned, then you could have a card status field in your cards table indicating whether sold or available for sale.
You could probably handle the returns by using the invoice table and having an invoice type field with types such as 'New Sale', 'Returns', 'Resale' etc, the original invoice number, return date etc and then add the specific cards that were returned which would be recorded in returns table and as each is added you reset the card status back to 'available for sale'. If the reason for return will always be the same for all, then this can be a field in the return Invoice table, but if the reason can be different for each card then would have to be a field in the returns table. I don't understand your why you would want to make Inv_ID NULL as this is probably a key to another table and can't be NULL, causing no end of problems plus you'd lose accountability
David

 

[silversun]

Hi,
Thanks for your helps. I modified my tables and relationships. As you can see in the attached image the inv_ID in tbl_allPins is a FK. Removing the inv_ID helps me to know that I can sell it to another vendor in a new invoice. I do not think adding another field for available cards is right, because it looks like repeating info in a table twice. Right now I have written a VBA to write serial numbers in tbl_return and removing inv_ID from tbl_allPins but I am being given "Run time error 91". If you look at the VBA code I am using then you can simply find where the error is and I can not see it. Please open the attachment and look at the relationships.

You will definitely see what I am looking for. Also my VBA procedure is added here:
Private Sub cmdAdd_Click()
Dim str_srl_num As Long
Dim to_srl_num As Long
Dim CurSrlNum As Long
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
'*set the variables and database
str_srl_num = Me.serialFrom1
to_srl_num = Me.serialTo1
CurSrlNum = str_srl_num
Set db = CurrentDb()
Set td = db.TableDefs!tbl_returns
Set rs1 = td.OpenRecordset
'*update a range of serial numbers
Do Until CurSrlNum > to_srl_num
'add returned serial numbers
rs1.AddNew
rs1!serial = CurSrlNum
rs1!inv_num = Me.cbo_invNum
rs1!Reason = Me.cbo_reason
rs1.Update
CurSrlNum = CurSrlNum + 1
Loop
rs1.Close
Set db = Nothing
Set rs1 = Nothing
'*-------------------------------------------------------------
' If there are no values to process, exit the procedure:
' If IsNull(serialFrom.Value) Or IsNull(serialTo.Value) Then
' Exit Sub
' End If

' Declare a query definition object and reference the query "qry_remove_invID":
Dim qry As DAO.QueryDef
Set qry = db.QueryDefs("qry_remove_invID")

' The query needs 3 values. These have to be set from left to right:
' First, the value which has to be updated:
qry.Parameters(0).Value = Null

' Second the value of 'SerialFrom':
qry.Parameters(1).Value = serialFrom.Value

' Third the value of 'SerialTo':
qry.Parameters(2).Value = serialTo.Value


' Run the update query:
qry.Execute

' Selfexplaining (I hope...)
If qry.RecordsAffected = 0 Then
MsgBox "There were no records to be assigned.", vbExclamation
End If



End Sub

 

[silversun]

Hi all,
I was able to figure it out. The error was given because I had closed db before I started the query "qry_remove_invID".