Update or append query, which one is my solution?

[silversun]

Hi all,
Please help!
I have a DB with several relational tables. I want to add new records to a table (tbl_returns). To add new records (serial number of returned cards) I am using a regular form. There is a serial number for each returned card. The problem came up when I wanted to add more than one record each time.
For instance a range of serial numbers between 6729 and 6731 & another range of 8213-8219 are returned, must be recorded in tbl_returns.
There is already an inv_ID for each returned card in tbl_allPins showing that it was sold before. Thus inv_ID in front of each serial number in tbl_allPins must be removed (assign to NULL) as well.
Normally each card is returned once, but in some cases returned cards can be sold somewhere else and they still can be returned. Sell's date and date of returning cards is always different.
Therefore serial numbers in tbl_returns is not necessarily a unique number!
A copy of DB is attached.View attachment DB4Test_mod1.zip
Thanks for your time

 

[jonathanchye]

Good code However when using recordsets it's good practice to close when done. So something like

rs.close
set rs = nothing
db.close
set db = nothing

when closing database or form.

 

[silversun]

Hi Pat Hartman,
Thanks for your respond. It took me a long time to find variables in your code and switch them with matched variables in my form. Anyway, there are some parts missing that I have no idea how to complete them. If you look at the form "frm_returns " in my database I am sure it is pretty simple for you to fix it. I just removed some validation parts temporarily to make it simple and easy to understand for myself. Please Let me know if I am doing something wrong.
Please look at the attachment including all the VBA codes I modified in a .txt file.
View attachment VBAanswers2.txt
I did not understand line 20, 27 and 30 so I did not modify them but I'm sure they need modifications.
Thanks for your helps in advance.

 

[silversun]

Hi Jonathanchye,
I had a problem and someone gave me a set of VBA codes to fix it and you completed it. Now I have difficulty when matching variables in my database. Can you please help me on that? If yes, please look at the questions and answer in this thread.
Thanks in advance.