Combo Box Autopopulation with an import button across databases

[June7]

My understanding your business process is useless if I can't understand why your table refuses to accept record import. Since you cannot provide actual database, it is a mystery I cannot solve. You might have to use a consultant who can visit you on site.



I am confused by table names again. Is Main table and TblMastertube the same table? Again, really need to provide complete db schema. That's called an ERD - Entity Relationship Diagram - do research.

Is MainID generated by autonumber type field?

If you want an option to overwrite an existing record, then that will mean more code. Code to search for and set focus to existing record, code to select Heat from remote table, code to then pull data from remote record to existing record.

[Vita]

My understanding your business process is useless if I can't understand why your table refuses to accept record import. Since you cannot provide actual database, it is a mystery I cannot solve. You might have to use a consultant who can visit you on site.

I am confused by table names again. Is Main table and TblMastertube the same table? Again, really need to provide complete db schema. That's called an ERD - Entity Relationship Diagram - do research.

Is MainID generated by autonumber type field?

When I said main table I was referring to the Main database, my apologies but TblMastertube is the main table in the main database.

Yes, MainID is generated by an autonumber type field.

If you want an option to overwrite an existing record, then that will mean more code. Code to search for and set focus to existing record, code to select Heat from remote table, code to then pull data from remote record to existing record.

Can append not overwrite?
I was thinking I could do something like link the [TblMastertube] inside [QappImHeat] and have a criteria so that it will only write to a record that matches [TblMastertube.MainID]. That way it would just overwrite whatever heat is linked to that MainID.
Would that not work?

[June7]

APPEND does not overwrite, it creates a new record. UPDATE does overwrite.

Yes, an UPDATE action can involve a link between two tables and can limit the update to a specific record. Syntax is like:

UPDATE tableA SET tableA.field1 = tableB.field1 FROM TableA INNER JOIN tableB ON tableA.fieldname = tableB.fieldname WHERE tableA.fieldname = [comboboxname]

[Vita]

APPEND does not overwrite, it creates a new record. UPDATE does overwrite.

Yes, an UPDATE action can involve a link between two tables and can limit the update to a specific record. Syntax is like:

UPDATE tableA SET tableA.field1 = tableB.field1 FROM TableA INNER JOIN tableB ON tableA.fieldname = tableB.fieldname WHERE tableA.fieldname = [comboboxname]

Good to know. I think UPDATE is what I want then. It seems like it would be more suitable to the current usage.

[Vita]

APPEND does not overwrite, it creates a new record. UPDATE does overwrite.

Yes, an UPDATE action can involve a link between two tables and can limit the update to a specific record. Syntax is like:

UPDATE tableA SET tableA.field1 = tableB.field1 FROM TableA INNER JOIN tableB ON tableA.fieldname = tableB.fieldname WHERE tableA.fieldname = [comboboxname]

Now that you mention this it makes me think that since appending creates a new record it is not filling in the other required fields such as the part name. Perhaps that is why I am getting a validation error and I need to use update instead.
I will do some testing and come back with results.

[orange]

Here you are - 50 posts in and readers don't really know your table structure, or your validation rule(s) or the associated business process(es).
Readers need some real information in order to quit guessing and provide focused (even tested) advice/options. Without something real being posted, your best option, as June mentioned, is to seek an onsite consultant.

[Vita]

Here you are - 50 posts in and readers don't really know your table structure, or your validation rule(s) or the associated business process(es).
Readers need some real information in order to quit guessing and provide focused (even tested) advice/options. Without something real being posted, your best option, as June mentioned, is to seek an onsite consultant.

Well there are no validation rules to my knowledge but as I have said I am stuck because my employers do not want me sharing the DB. I would gladly attempt to erase all the confidential data and upload the DB but it is not up to me.
And I am working on a DB that was maintained by 2 people prior to me. I understand your frustration because believe me I just want this to work too. Although you should know that all of this has helped me learn a lot.

I also did upload a mockup of the part that I am trying to add just like 10 posts ago. I will probably end up going to a consultant if my current idea does not work.

[orange]

Vita,
There is a validation error shown in #35 and a reference in #37 --something is triggering an error.
There is no confidentiality lost in showing your tables and relationships. The data values in context would/could be an issue.
I understand the data part of the concern of management, but if readers are to offer focused help we need facts and some context to attempt some options.

If readers had an empty database with your tables and relationships, and NO DATA. Structure only, then perhaps we could work through a few mockup records of your choice and research the issue. Failing that, then the consultant may be your best option. You may want to discuss options with the employer.
Good luck.

[Vita]

APPEND does not overwrite, it creates a new record. UPDATE does overwrite.

Yes, an UPDATE action can involve a link between two tables and can limit the update to a specific record. Syntax is like:

UPDATE tableA SET tableA.field1 = tableB.field1 FROM TableA INNER JOIN tableB ON tableA.fieldname = tableB.fieldname WHERE tableA.fieldname = [comboboxname]

I am attempting to swap over to UPDATE using the code below:

UPDATE TblAttHeats SET TblAttHeats.HEAT = [Lots]![Heat], TblAttHeats.C = [Lots]![C]WHERE (((TblAttHeats.HEAT)=[Forms]![LOTS]![LotsCboImHeat]));

I tried reading up on INNER JOIN but I got confused. It just reminded me of append.
My current code above brings up a prompt for me to enter the value for [TblMastertube]![Heat] and then [TblMastertube]![C]. I do not know why. I am thinking it either cannot find the table [TblMastertube].

[Vita]

Vita,
There is a validation error shown in #35 and a reference in #37 --something is triggering an error.
There is no confidentiality lost in showing your tables and relationships. The data values in context would/could be an issue.
I understand the data part of the concern of management, but if readers are to offer focused help we need facts and some context to attempt some options.

If readers had an empty database with your tables and relationships, and NO DATA. Structure only, then perhaps we could work through a few mockup records of your choice and research the issue. Failing that, then the consultant may be your best option. You may want to discuss options with the employer.
Good luck.

That is the default validation rules. It was something with primary key. None of the fields have required validation rules.
I am going to talk to them when I can but I am trying to do what I can right now.
As I stated earlier I think the fact that append was trying to create a new record without some required fields was causing a validation error and that UPDATE is the one that I need to use. I was unaware append created a new record everytime.

[June7]

Just setting a field in table as "required" is a validation rule. If none (other than primary key) are set as "required", then that cannot be cause of issue.

There is no JOIN clause and TblMastertube is not referenced in that SQL. Is that the complete statement? Why is the WHERE clause up against [Lots].[C]?

Are you using the query designer to construct query?

[Vita]

Just setting a field in table as "required" is a validation rule. If none (other than primary key) are set as "required", then that cannot be cause of issue.

There is no JOIN clause and TblMastertube is not referenced in that SQL. Is that the complete statement? Why is the WHERE clause up against [Lots].[C]?

Are you using the query designer to construct query?

Yes I was using the query designer to construct the query. I am not sure why but that code was the wrong one. Here:

UPDATE TblHeatsMaster SET TblHeatsMaster.Heats = [Forms]![FrmMastertube]![Heat], TblHeatsMaster.C = [Forms]![FrmMastertube]![C]
WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

It is giving me a key violation. [TblHeatsMaster] has a primary key of Heats. [FrmMastertube] shouldn't have a primary key but [TblMastertube] has a primary key of ID.

[June7]

Well, joining on the same field you want to update doesn't really make sense. And your SQL doesn't even have JOIN clause. Your attempt does not follow my example syntax.

UPDATE TblHeatsMaster SET TblHeatsMaster.C = TblMastertube.C
FROM TblHeatsMaster INNER JOIN TblMastertube ON TblHeatsMaster.Heats = TblMastertube.Heat
WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

[Vita]

Well, joining on the same field you want to update doesn't really make sense. And your SQL doesn't even have JOIN clause. Your attempt does not follow my example syntax.

UPDATE TblHeatsMaster SET TblHeatsMaster.C = TblMastertube.C
FROM TblHeatsMaster INNER JOIN TblMastertube ON TblHeatsMaster.Heats = TblMastertube.Heat
WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

I don't exactly understand inner join. I tried to read on it. I will copy what you put here though!
I had used the query wizard to make a query then swap it to an update query. That's probably why it lacked the join.

Edit: I get a syntax error when trying to use that code. I don't know much about SQL so I am not sure what is missing.

[June7]

What is it you don't understand about JOIN?

When using the designer to build, if there is no link line connecting tables, will have to manually add it by clicking on key field and dragging to corresponding field of other table.

So, first build a SELECT query that has JOIN and WHERE clauses - make sure appropriate record(s) are retrieved - then switch to UPDATE to complete. Then look at SQLView to see statement.

The syntax I provided was per a site I reviewed https://www.geeksforgeeks.org/sql-update-with-join/. I did not test it.

There are many online tutorials for SQL.