I'd like to thank this community for already helping me so much with this project. I knew nothing about databases or access when I started a few days ago and I'm almost done with the 'hard' part... i think.
Anyway on to the problem. I have a 'product' table I'm trying to fill out with a form for the user. This Table contains:
ProductID - primary key
I created a form with 'Platform' as the main part with a subform of 'Product' from their respective tables. The subform I'm using is data view since it's layout, a spreadsheet, is very common to the people who will be using it.
The issue is when you make try to place a Game in that does not yet exist in the 'Game' table you get the 'not in related entry' error. For single entries this was easy, I just used the list box and the NotInList trigger to write the required code to add the entry into the Game table and all works well. The only issue is if you copy/past a list of Games and try to add it to the table it seams to completely skip the form all together and pops up with the 'not in related entry' error and than creates the 'Past Error' table.
So my question is how do I make this work? How do you append a table when you get this error when copy/pasting allot of data in(200 lines or so).
My idea was to capture the error, 3201 i think it is, and make some code based on that but I don't know how to implement that into a form, or even the table for that matter. I like this idea because I would also like to suppress an index duplication error so that it just doesn't create the entry. I can't expect people to go threw hundreds of records to see if one of the hundred they are trying to add is a duplicate.
The 'Game' 'Platform' thing was to simplify the discussion without having to explain the technical details of my job ....
Possible Relevant Table info:
GameID - One to Many ProductTable.GameID
PlatformID - One to Many ProductTable.PlatformID
ProductID - autonumber
GameID - combined index with PlatformID
I'm not sure to fully understand. Where does the Game thing come from (in: "try to place a Game in that does not yet exist in the 'Game' table") while you're describing a main form bound to the Platform table and a subform bound to the Product table ("I created a form with 'Platform' as the main part with a subform of 'Product' from their respective tables")?
Anyway, if you want to copy several rows of data from the subform into new rows in its bound table, you'll have to write some code. The easiest and quickest solution would be to assemble an UPDATE SQL statement and have it executed. You know which rows from the table are displayed in the subform, this would be WHERE clause of the query.
The main form of Platform looks like:
Plantform [entry box]
-----------Than a subform to input games that is in data sheet view that looks like:
This is used to update a table called 'Product' that has entries for Platform and Game. With this form you can choose a platform say "PC" in the main part of the form and past in list of games in the sub-form. So if you pasted in 3 games "Call of Duty", "Crisis", and "iRacing"
it would make an entry into the 'Product' table like....
PC--------------Call of Duty
The problem is if these Games do not exist in the 'GAME' table it throws out the 'not in related entry' error. I need to find a way to update the 'Game' table for games that do not exist. I also want to suppress the 'duplicate index' error when a someone inputs a 'Platform'-'Game' combination that already exists.
would it be easier/better to make a separate blank table to hold the values and use a 'submit' button to run an UPDATE SQL statement. It seams silly since it would essentially be the same info as the current table but it would allow me to hold these values without actually updating the real table. This way I could past all the values in than since I'm loading them with VB I could check that each game exists first make sure the PC--GAME combo doesn't exist, than load the table?
The problem with direct entry into the table is I don't know how hold off the update. And the second problem is I can't trigger code from the form when copy/pasting a value. It seams like when you copy past into the sub-form you bypass the form completely as if you were copying strait into the table itself.