Combo Box Autopopulation with an import button across databases

[Vita]

Cannot use Me. reference in query objects. It is valid only in VBA. It is shorthand for the form or report name the code sits behind. The code I suggested would be in an event procedure such as a button Click or combobox AfterUpdate.

Still unclear to me how these tables should work together - what data needs to be pulled from where and saved where. Maybe a junction table is not need because data is not in a many-to-many relationship but a master and child table might be needed for a one-to-many relationship. Really should provide sample data even if you can't provide a db file - can build tables directly here in post. Go to the Advanced post editor and you will have some table building tools available. Can also copy/paste from Access table then edit with the table tools.

I don't think it was me who advised to change table naming but I agree with it.

Well I am glad you agree with it and I apologize for changing them and confusing you.

So TblMetalDBHeats is our subsidiaries table. They manufacture the parts so they input the Heat and Chemistry into that table for record keeping and their own inventory purposes.
Main table is our table where a team of people use the Main form to input newly arrived stock so that we can use a separate form(name is irrelevant) to find it in our warehouse.

The chemistry data consists of 26 fields I believe. (Not every field is filled so I have "allow zero length" set to "yes" so that nulls can be copied in. I wanted to use an if statement to check but this version only has IIF and I didnt know where to put it)
Up until now they enter every field manually. My objective is to make a dropdown list with all the heats so that they can click which one they want and then a import button for confirmation.


This will save time and reduce human error.

The following is what I originally did before reading online that a junction was the proper way to do it.

So I created a combobox [FsubCboImHeat] that has a row source of the query named [QlkpImHeatCbo] and has an "On exit" event procedure with the following code

If Me.Dirty Then
    Me.Dirty = False
End If

Which is supposed to force the table to update. I got the code from another forum.

and then I created the button next to it named "ImHeatBttn" using the button wizard to have it run the query [QappImHeatBttn].
So the SQL linked to the button is as follows:

Private Sub ImHeatBttn_Click()
On Error GoTo Err_ImHeatBttn_Click


    Dim stDocName As String


    stDocName = "QappImHeatBttn2"
    DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_ImHeatBttn_Click:
    Exit Sub


Err_ImHeatBttn_Click:
    MsgBox Err.Description
    Resume Exit_ImHeatBttn_Click
    
End Sub

I still have all this code and can just delete [TblChemJunc] and [ChemistryJunctionF] and then change the query targets.
I had a separate issue using that original system though.

[June7]

Zero Length string is not same as Null. I NEVER allow ZLS. If your source data has empty strings then I suppose you must allow.

You wanted to use IIf to check for what?

So far I think I understand this much:
Remote table TblMetalDBHeats has Heat (a record identifier ?) and Chemistry (26 fields) data.
Remote data must be transferred to local Main table.
There are 26 fields of chemistry data but so far queries you posted copied only two fields.
Users enter data manually - but what data - 26 chemistry fields?

When you build a report will you need to use the remote table?

[Vita]

Zero Length string is not same as Null. I NEVER allow ZLS. If your source data has empty strings then I suppose you must allow.

You wanted to use IIf to check for what?

So far I think I understand this much:
Remote table TblMetalDBHeats has Heat (a record identifier ?) and Chemistry (26 fields) data.
Remote data must be transferred to local Main table.
There are 26 fields of chemistry data but so far queries you posted copied only two fields.
Users enter data manually - but what data - 26 chemistry fields?

When you build a report will you need to use the remote table?

You are correct. Heat is a kind of record identifier.
Yes there are 26 fields but I was testing with only 1 because I wanted to get it working before adding the rest.
Yes users enter the 26 chemistry fields manually.
If by build a report you mean add a record then yes because they will need to import the heat when making a new record.

I wanted to use IIF instead of ZLS. For example IIF(Len(C) = 0 or "", "", C). (Excuse my syntax as I didn't look it up but hopefully you get the idea)
So that way it would write a blank string or skip that field entirely because I also read that you shouldn't put ZLS on unless you need to.

[June7]

By "build a report" I do NOT mean "add a record" - forms are used for data input, reports are used for data output. If your intent is to import all fields from remote table then that table will not be needed for building reports and the "junction" table would not appear to be need. It is virtually impossible to build a db without use of junction tables so there is most likely a many-to-many relationship somewhere in your schema.

If you don't want to allow ZLS and source data might include, the conditional expression would need to return Null or data, not "":

IIf([C] = "", Null, [C])

If you wanted to use Len() function as well (redundant):

IIf(Len[C] = 0 Or [C] = "", Null, [C])

It is possible, but unlikely in this case, that source data could contain non-printing characters. This expression would not catch those and they would import so field could appear blank but be neither empty nor Null.

Only text type field can hold empty string so would only need the conditional expression for those, not number or date/time.

[Vita]

Okay then I think we are on the same page to delete [TblChemJunc] and [ChemistryJunctionF]. So I went ahead and did that.

I recreated the combobox for selecting a heat and the import button that points to QappImHeatBttn.
I also updated the QappImHeatBttn query to append to the main form[Lots] instead of [TblChemJunc]. I gave it the criteria of "[Forms]![LOTS]![LotsCboImHeat]" (The combobox for selecting the heat).

Now when I have the query design open it shows me that the results work.

But when I go to actually use it normally with the query close I get this validation rule error even though (I double checked) ZLS is on for all the fields.


And I should mention that the data being copied is decimals up ten-thousandths place(.0004). No odd characters.

Here is the SQL for the QappImHeatBttn query:

INSERT INTO Lots ( Heat, C )SELECT TblAttHeats.HEAT, TblAttHeats.C
FROM TblAttHeats
WHERE (((TblAttHeats.HEAT)=[Forms]![LOTS]![LotsCboImHeat]) AND ((TblAttHeats.C)=IIf([C]="",Null,[C])));

[June7]

Perhaps should be:

INSERT INTO Lots ( Heat, C ) SELECT HEAT, IIf([C]="",Null,[C])
FROM TblAttHeats
WHERE TblAttHeats.HEAT=[Forms]![LOTS]![LotsCboImHeat];

However, if both [C] fields are number type, the IIf() should not be needed.

[Vita]

Still gives the same error "Validation rule violations".
I had an error(That I don't remember how to recreate) while I was originally creating ChemJunc that led me to think the append query thought the Heat was null which makes no sense.

The [Heat] Field and the 26 Chemistry fields are all text fields and they all have ZLS set to yes.

So I am befuddled by this. I thought it might have something to do with relationships but I am lost because it seems like it should work.

[Bob Fitz]

Can you post a copy of the db

[Vita]

Can you post a copy of the db

I would like to but I don't have permission from my employer nor do I have any idea of how I would go about that because it is 2 databases and the main database is split between frontend and backend.

[June7]

Either provide multiple databases or convert the linked table to a local table which could act as the remote source.

Doesn't have to be your actual working db file. Could build a mockup that replicates the structure but doesn't have to be the actual company data.

I have never understood why employers object. We don't know your company and the data is just letters and numbers to us. If there is any sort of confidential info (addresses, phone numbers) replace it with dummy data.

Are any fields in Lots set as required?

[Vita]

Either provide multiple databases or convert the linked table to a local table which could act as the remote source.

Doesn't have to be your actual working db file. Could build a mockup that replicates the structure but doesn't have to be the actual company data.

I have never understood why employers object. We don't know your company and the data is just letters and numbers to us. If there is any sort of confidential info (addresses, phone numbers) replace it with dummy data.

Are any fields in Lots set as required?

I made a mockup of the database with some chemistry samples. Since I made this from scratch it does not have all the extra data stuff from the previous maintainers.
Unfortunately though my mockup works fine and does not give me the violation error even though everything is exactly the same (Except for the extra stuff)
MockupDB.zip

My other issue is that the fields don't seem to update after I import the data.

[June7]

Why are you saving numeric data to text fields? Even if the source data is text, recommend saving in local table as number.

Again, combobox for selecting Heat to import should be UNBOUND, otherwise you change data in TblMastertube record. Then code imports that Heat but there is already a record with that Heat because you just changed record with combobox so end up with duplicate Heat and a corrupted record.

Otherwise, code works.

Now if you want to view that imported record on the form, have to requery the form then either apply filter or go to record.

If you want to eliminate the popup warnings:

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Or use CurrentDb.Execute and build SQL statement in VBA.

[Vita]

Why are you saving numeric data to text fields? Even if the source data is text, recommend saving in local table as number.

Again, combobox for selecting Heat to import should be UNBOUND, otherwise you change data in TblMastertube record. Then code imports that Heat but there is already a record with that Heat because you just changed record with combobox so end up with duplicate Heat and a corrupted record.

Otherwise, code works.

Now if you want to view that imported record on the form, have to requery the form then either apply filter or go to record.

So to make it unbound I just remove the control?
How do I make it so the textboxes automatically refresh/requery upon importing?

If you want to eliminate the popup warnings:

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Or use CurrentDb.Execute and build SQL statement in VBA.

I would add these lines of code to the import button?

Unfortunately the clone of the actual database still has a validation error. Since everything I have done works that means it must be something else from the previous maintainers that is stopping it from appending right?

Also is there anyway I can get the heat to overwrite the current heat without creating an entirely new record? Currently it creates a new record with a new autonum.

[June7]

Remove field reference from combobox ControlSource property.

Refresh/requery form's RecordSource with: Me.Requery. You had Me!C.Requery in event and that is trying to requery field C.

Now to view the new record would require more code. Depends if you want to filter or just go to record. Here is one way:
Me.Requery
Me.Heat.SetFocus
DoCmd.FindRecord Me.ImHeatCbo

Yes, SetWarning lines are added to code for the import button, as would be the above suggested code.

To overwrite, one method would be an UPDATE action SQL. But why would you want to overwrite? Your mockup db does not show any dependent tables. What do you do with this Heat data once it is imported? Still don't know your db schema.

Set Heat field as Indexed (No Duplicates) to prevent duplicate Heat records. Could also set up combobox RowSource so only Heat nums not in TblMastertube will list.

Code modules should have Option Explicit in header. Set this as default in VBA editor > Tools > Options > check Require Variable Declaration.

[Vita]

Remove field reference from combobox ControlSource property.

Okay I did this

Refresh/requery form's RecordSource with: Me.Requery. You had Me!C.Requery in event and that is trying to requery field C.

Now to view the new record would require more code. Depends if you want to filter or just go to record. Here is one way:
Me.Requery
Me.Heat.SetFocus
DoCmd.FindRecord Me.ImHeatCbo

Yes, SetWarning lines are added to code for the import button, as would be the above suggested code.

Okay so I changed it to Me.Requery and it works now. I do not understand the rest of what you said here, I apologize.
Is this for like if I was entering multiple records? I did test it and it updated to import the heat but if I decide to import another heat without closing out the form first then it does not update to the new heat which is why I want it to overwrite.

Let me explain a bit more. We manufacture metal parts. Our subsidiary buys steel and turns it into a specific form before sending it to us. The heat is basically the chemistry ID. They input the heat and chemistry data into MetalDB/HeatsMaster and this will auto import it. However we get parts from other companies and need to be able to enter the heat from them into the same table which is why I am using append so that the quality team can also manually input heats.

The heat and chemistry is only part of the bigger form which stores more information about the parts we have in stock. There are two forms the edit form and the search form. We are adding this import feature to the edit form so that the quality team can save time and just import heats from our subsidiary.

In the main table each "lot/part we receive from our subsidiary gets a designated ID along with all the other relevant information which includes heat and chemistry. So heat is almost like a subID kinda.
For example:
ID: 12056 (We can call this MainID for reference?)
Partname: steelpart5
size: 10ft
Amount: 1000ft
Heat: A1005
Chemistry fields: (insert 26 fields)
and theres other stuff.

To overwrite, one method would be an UPDATE action SQL. But why would you want to overwrite? Your mockup db does not show any dependent tables. What do you do with this Heat data once it is imported? Still don't know your db schema.

Set Heat field as Indexed (No Duplicates) to prevent duplicate Heat records. Could also set up combobox RowSource so only Heat nums not in TblMastertube will list.

Code modules should have Option Explicit in header. Set this as default in VBA editor > Tools > Options > check Require Variable Declaration.

So I need it to overwrite so that they can select a MainID and overwrite the existing Heat & Chemistry. Incase they click the wrong option or there were past typos.


Unfortunately though it seems the real table is having validation issues. Since this version I sent you works fine it leads me to believe that something the previous maintainers did is causing a validation issue.
There are required fields but I tested importing/appending on a 'dummy' lot which had all the information filled in except the chemistry and it still had the issue.

I hope this helps you understand a bit more. Also keep in mind that as I am new to this position and learning(and this is basically a test to see if I can juggle this with my other duties and still figure it out) that I don't know much else of how the database was made and the guy who was teaching me/made most of it passed away recently which is why I am taking over. I plan to make changes in the future hopefully with some R&D.