Add Entry to a Table Unless Already Exists, VBA

[nick404]

I have a form where a user reports issues occurring with raw materials.



If the commodity they are using and(or) defect/non-conformance they are experiencing isn't already included in the combo boxes (have some base commodities and defects set up, but thinking long term use right now) they can click a button to go to another form to enter in a new defect and associated commodity. They click 'add' and access will add the defect and commodity into the db. It works well, except when I try and add a defect to an already existing commodity.

I have two separate tables germane to this: tblCommodity with 'commodity' as the PK, and tblNonConformances with fields 'defect' & 'commodity', and an auto# PK. 1-Many rship on the field 'commodity'. Now when they add a defect to an existing commodity I get an error since I am attempting to add a duplicate PK. This is my current code behind the add button:

Sub btnAdd_Click()
On Error GoTo Err_proc0
Dim B As Object
Dim V As Object

Set V = CurrentDb.OpenRecordset("tblCommodity")
V.AddNew
V![Commodity_PK] = Me.txtCommodity.Value
V.Update
Set V = Nothing

Set B = CurrentDb.OpenRecordset("tblNonConformances")
B.AddNew
B![NonConformance] = Me.txtNonConformance.Value
B![Commodity] = Me.txtCommodity.Value
B.Update
Set B = Nothing

Forms!frmIssueEntry!cboCommodity.Requery
Forms!frmIssueEntry!cboDefect.Requery

DoCmd.Close
Exit Sub

Err_proc0:
MsgBox Err.Description
Exit Sub

End Sub

Is there a way I can get the entry to add to tblCommodity only if the commodity entry does not already exist? (but still add the 'defect' and 'commodity' to the tblNonConformances)
Let me know if I can help explain this better.

[ItsMe]

I guess the issue you are having is a fail here?
V![Commodity_PK] = Me.txtCommodity.Value
V.Update

I suspect that Commodity_PK is the Foreign Key? What is the name of the Primary Key for tblCommodity? Is it an auto number?

[nick404]

That would be the spot that is giving me issues yes.
No, Commodity_PK is the primary key for the tblCommodity. The foreign key is the commodity field from tblNonConformances. (both are purple color in my coding)
Here's a shot of my relationships for better reference:

[ItsMe]

I need to step away from my desk for a while...

What does not make sense is the One to One between tblMasterData and tblNonConfomance. Also, the idea of using a tblNonConfomance_1 creates additional questions in my head. So, offering solutions to your original question is difficult. Adding records to a table is very common and any solution will be a conventional solution. I am having a hard time applying a conventional solution to your model.

What I can see is you need an additional field that offers the description of the Commodity. I am not sure where to put this and it seems you may need to reorganize your tables. With an extra field (let's say to the tblCommodity), you can then employ the AutoNumber type to the PK. When you add a record, add a value to the additional (new) field. This should be something that humans can understand. Before adding a new record, use a DLookup() function searching for the text in the new Column/Field.

[nick404]

I can delete those relationships.. They were more or less only intended to tell access "these things are related", but if it causes more problems than what it helps I will eliminate them. The tblNonConformances_1 was generated by access in the relationships page because I tried to make a relationship between the commodity fields and it put an additional table in. tblNonConformances_1 is not a physical table I have made in my db.

The commodity is a text field, e.g. Substrate, Canisters, Case Labels, etc. Do you mean I should set 'Commodity' as a auto# and use a 'CommodityDescription' as Substrate, Canisters, &c.?

[ItsMe]

...The commodity is a text field, e.g. Substrate, Canisters, Case Labels, etc. Do you mean I should set 'Commodity' as a auto# and use a 'CommodityDescription' as Substrate, Canisters, &c.?

Ideally, you want AutoNumbers as PK's and Long Integers as FK's. So, yes, you want to have a field of type Text for the Humans and a field of type AutoNumber for the Computer. Store the PK value in the relative FK field.

As for deleting the relationships or whether or not they are causing problems, you don't have to make any adjustments. Just be aware of any Constraints and or Referential Integrity rules enforced via the Relationship properties. I now understand why there is a table tblNonConformances_1. I don't use the Relationships Window and forgot that Access will create names like that.

[nick404]

So if I were to add in an AutoNumber primary key right now, wouldn't that create an issue with my current 1- many relationship between tblCommodity and tblNonConformances?

[June7]

Yes, if records already exist. Will have to UPDATE a new FK field in the dependent table, then delete relationship so the old FK can be removed and build link on new PK/FK fields.

It's that or designate the human-oriented unique identifier as the PK and the autonumber fields are not needed. Whether or not you use autonumber or human-oriented identifier as PK is a design decision depending on circumstances and preferences. I have done both.

I am also confused by some of the relationships. Why is Defect linked to NonConformance? The PK in tblNonConformance is Non_Conf_Commod_PK. Same for the two Commodity fields. Links need to include PK fields.

[ItsMe]

Yes, you will need to run Action Queries against your tables to Update the relevant fields with the appropriate keys. I do this by making a copy of the BE and storing it local, on my C drive. Then, I will create a new FE file. In the FE file I will import the tables I plan on changing. I will make updates to the tables and keep notes in Notepad and Excel.

I will start to create queries that will Append and or Make Table. Depending on the data, I may use VBA to execute the Updates or I will use the Query Designer. I will test with tables local to the FE. Then, I will delete the local tables and import new ones from the BE copy on my C drive each time I run a new test.

Next, I will use my notes to edit the BE file on my C drive. I do this because I will likely perform the production cut via linking. I will want to test the linking process on the copy BE first.

Since your scenario involves adding an Autonumber, you may need to copy one of your Action queries over. There may be a combination of using an Action query in your BE and Designing a new table or just running a Make Table query. I may, as a third option, employ VBA from the FE to automate the entire process. Ultimately, you need to do what you are comfortable with. I prefer to automate the entire process because I can use a test environment as I develop automation and then flip the switch for the Production environment. If the process is not fully automated, you will have to depend on your notes to perform "mechanical" operations, e.g. create a table.

Finally, I will use the Wizard to link to the tables on my C drive from my FE, for final analysis. I will use existing forms from the Production FE file to do searches. I may also build SELECT queries to analyze the data.

It's hard to provide a perfect description of the various steps since there are options between having code do everything and having code do, only, some parts.

[nick404]

In response to June,

*Database hasn't gone live yet so only dummy data currently.*

The wording got changed when I was handed the database from a previous owner, but "defect" and "non conformance" are same thing- different wording. "Defect" is used pervasively and "Non Conformance" is only seen one or two times.
The primary key for tblNonConformance is 'Non_Conf_Commod_PK' and it is an auto# for each pairing of a Defect + the corresponding Commodity. I wanted to make the Defect the PK but could not since a few defects occur to multiple commodities. For example the defect "Holes" is experienced with both our commodities "Bags" and "Canisters".

As I kind of stated earlier, the links between non-pk fields was me trying to tell Access that those fields were related. I don't know if that actually helps anything or not but at the time thought it was a beneficial addition.

[June7]

Not beneficial and will cause confusion when building queries. Access will automatically use Relationship links when you build a query then you will have to modify the query joins as you see fit. This is a reason why many experienced developers don't even bother with Relationships.

[nick404]

Yes, you will need to run Action Queries against your tables ... It's hard to provide a perfect description of the various steps since there are options between having code do everything and having code do, only, some parts.

Thank you for the detailed description. I do appreciate it.

[nick404]

Not beneficial and will cause confusion when building queries. Access will automatically use Relationship links when you build a query then you will have to modify the query joins as you see fit. This is a reason why many experienced developers don't even bother with Relationships.

OKay, thank you for the insight!