Help with DLookup

[dkotula]

Hello,
I have a DB with linked tables to ODBC, and queries based on them. I have a form with 3 cascading combo boxes and tabs containing subforms. Along with the combo boxes on the main form I have a txtJobNum, and a txtVendorNum. The subforms I have, are Job and Vendor. In subform Job, I have txtJobNum, and in subform Vendor I have txtVendorNum. What I did was use columns from the last combo box to populate txtJobNum and txtVendorNum on the main form. To get txtJobNum and txtVendorNum to replicate in the corresponding subforms, I used DLookup. How do I get the rest of the fields to populate in my subforms? Also, I get an error with any records that come back null, how can I get the DB to ignore this?

Private Sub cboRelease_AfterUpdate()
    Call Find_WithFilter
    Me.txtJobNum = Me.cboRelease.Column(1)
    Me.txtVendorNum = Me.cboRelease.Column(2)
 
Dim strJobNum As String
strJobNum = DLookup("JobNum", "qryPart", "[JobNum]='" & Me.txtJobNum & "'")
Me!frmPart.Form.txtJobNum = strJobNum
 
Dim strVendorNum As String
strVendorNum = DLookup("VendorNum", "qryVendor", "[VendorNum]=" & Me.txtVendorNum & "")
Me!frmVendor.Form.txtVendorNum = strVendorNum
End Sub

[ChipR]

It sounds like you want your subforms bound to a recordset, and use the Link Master and Link Child fields of the subform to filter the records based on the value in the text box on the main form. For example,
Link Master Field: txtJobNum (on the main form)
Link Child Field: JobNum (a field included in the query)

The DLookup, as you are using it, appears to look up exactly the value you give it as an argument, accomplishing nothing.

[dkotula]

You are correct in saying filter from txtJobNum. My subform is already bound to tblPart, and gets the record from qryPart. The form and subforms are set to data entry. I'm trying to pull down records from the queries, and save to a table.

[dkotula]

ChipR:

In the subform, could I do something like txtJobNum on dirty to query based on information in txtJobNum?

[ChipR]

Setting the forms to Data Entry means that existing records will not be shown. Have you looked at the Link Master & Link Child fields?

[dkotula]

I have three tables thus far, that the form saves to based on selections made from a query. Main form is gets the PO#, Line#, Rel#, Job#, and Vendor# from qryPORel and saves to tblPONum. I achieve this by making a selection from the three cascading combo boxes that have a Row source pointing to qryPORel.

Subform frmVendor has a table "tblVendor that I want to save to, but I would like to get data from qryVendor. The main form has txtVendorNum that I could filter from. I currently have this subform bound to tblVendor, and all it's fields.

The subform frmPart is basically the same as above, with tblPart, and qryPart.

I am basically trying to get individual records from our ERP, based on the PO selections made in the combo boxes, and saving them to tables. I can then add additional information that our ERP DB does not provide, and I could also build additional features.

I really don't know, I may have this totally structured wrong.
Your input would be greatly appreciated.

[ChipR]

So you're trying to insert records in an automated way into a table based on the selections from the combo box? The subform would not be necessary for this, though it might be nice to show the records that have been added.

I would suggest a button on your form that triggers code to create a string with your SQL command to insert a record, and use DoCmd.RunSQL.

[dkotula]

I'm trying to achieve this in the after update event of the last combo box. I would also like to add additional fields to my tables, and form as I construct this. If possible I would like to keep the data seperated as I will probably link the tables together and have other forms with different functions like reporting or searching.
Could you provide me with a little tidbit of the DoCmd.RunSQL to get me on my way.
Here is what I'm using for the combo boxes in the row source:

SELECT DISTINCT qryPONum.PONUM
FROM qryPONum;

SELECT DISTINCT qryPONum.POLine
FROM qryPONum
WHERE (((qryPONum.PONUM)=[forms]![frmPO]![cboPO])) ORDER BY qryPONum.POLine;

SELECT qryPORel.PORelNum, qryPORel.JobNum, qryPORel.VendorNum, qryPORel.POLine, qryPORel.PONum
FROM qryPORel
WHERE (((qryPORel.PONUM)=forms!frmPO!cboPO) And ((qryPORel.POLine)=forms!frmPO!cboLine))
ORDER BY qryPORel.PORelNum;

[ChipR]

First, I have to point out that I don't consider adding fields to tables an option. If you need to do that, you designed the database incorrectly.

Also, consider that a user may select something from your "last" combo box before selecting items from the previous ones. It is much more user friendly to have a button that says "Hey, now I'm ready to set the code going to make all these automated changes to my database".

Here are the details on DoCmd.RunSQL Method [http://msdn.microsoft.com/en-us/library/bb214059.aspx].

Your code would be something like:

Dim strSQL as String
strSQL = "INSERT INTO myTable (field1, field2, field3) " _
 & "Values (" & cboPONum & ", " & cboPOLine & ", " _
 & cboOtherStuff.column(1) & ")"
DoCmd.RunSQL strSQL

So, you would take the values in the combo boxes and use them in the string to make an SQL command, which you run with the DoCmd.RunSQL. You may want to experiment with a very simple Insert statement without variables just to see how it works first.

[dkotula]

Thank you for the reply.
I regards to the combo boxes, I have them disabled, and each one is enable as the selections are made. cboPo is enabled, and cboLine, and cboRel are disabled. When someone makes a selection in cboPO, the cboLine is enabled. Then when a selection is made in cboLine, cboRel is enabled.

If I have the subforms bound to my tables, is there any reason I can't add additional fields to the table, and subform for additional information, and have it save to the record?

I will work on creating a SQL statement to populate the rest of the fields in the subforms. I'll keep you up to date.

Thank you
Dave

[ChipR]

When you say add additional fields to the table, I read change the table structure. Is this what you mean, or something else?

[dkotula]

Yes, this is still in the building stage, and may require me to add additional fields for manual entry in addition to the data I get from my queries.

I attached the DB so you can get a better idea of what I'm trying to achieve. I do relize that the linked tables won't work for you, but you can see the structure.

[ChipR]

You do not want to change the structure of the database. The building stage should only happen after the desin stage, when you determine your requirements.

[dkotula]

ChipR,
I have the contents of the main form working, I select PO, then Line, and then Release, and "txtVendorNum and txtJobNum" get populated. Like txtVendorNum on the main form, I have txtVendorNum on the subform "frmVendor" and would like the corresponding record associated with the Vendor number to populate the rest of the fields from qryVendor. I'm not real concerned with the way my main form functions as it is doing what it is supposed to do. I would like to have something in my event of the last cascading combo box (After Update) to run a query (qryVendor) and have it fill in the rest of the fields in my subform. This will in turn save to tblVendor based on the VendorNum in the main form.

See copy of my DB attached. I have not disabled the combo boxes in this one.

[dkotula]

I applied this based on what you provided, and recieved a sytax error INSERT INTO statement.

Dim strSQL As String
strSQL = "INSERT INTO tblVendor (Name, Address1, Address2, Address3" _
& "Values (" & txtVendorNum & ")"
DoCmd.RunSQL strSQL

I'm sorry as I'm not real proficient at this.

Dave

[ChipR]

You may want to insert the line

 MsgBox strSQL 

before the DoCmd.RunSQL to see the statement you have formulated before you try to run it. Access help has more information on how to use the INSERT INTO statement if you need it.

[dkotula]

Wouldn't I need something like this in a SQL statement:

SELECT "qryVendor.VendorNum,qryVendor.Name, qryVendor.Address1, qryVendor.Address2, qryVendor.Address3" FROM qryVendor _
WHERE ((qryVendor.VendorNum) = Forms!frmPO!txtVendorNum) _
Order BY qryVendor.VendorNum

[ChipR]

I like to use a hidden multi-column combo box on my form to hold information I need from another table. The source for the combo box would be your query. Then you set the combo box value equal to the value from the form, and access the associated data with comboBox.column(index).

Maybe you can explain fully what values you want to insert where, because I can't make any sense out of the bit that I've seen.

[NeoPa]

You have treated all your field names (references) together as a single string. You've also used VBA continuation characters in your SQL. Check through the following :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[dkotula]

Thank you for your replies, and I'm sorry for confusing you all.
The cascading combo boxes are used to get a unique PO number.
example:
12345-1-1
12345-1-2
12345-2-1
12345-2-2
Each one of these would be a record ID.
I have tables that link to ODBC progress DB.
I have created queries, to get data from the linked tables, and set some criteria to reduce the number of records queried. I don't need 20k records returned.
I created simple tables to except the data I get from from the queries based on the selections made on my form.
tblPO fields: ID, PONum, POLine, PORel, JobNum, and VendorNum.
tblPart fields: JobNum, PartNum, Description and etc.
tblVendor fields: VendorNum, VendorName, Address1, and etc

tblPO is bound to the main form.
tblPart is bound to subform frmPart.
tblVendor is bound to subform frmVendor.

The main form gets its data via the Row source of the combo box from qryPONum, and PORel.
cboPO = SELECT DISTINCT qryPONum.PONUM
cboLine = SELECT DISTINCT qryPONum.POLine
cboRelease = SELECT qryPORel.PORelNum
The reason for the two PO queries is because of the structure of the database that the records are coming from.
On the main form, there is also two text boxes that will be hidden: txtJobNum, and txtVendorNum. These are the identifiers for the records of Vendor and Job. I want to use these identifiers to run a query and populate the fields in the subform that correspond with txtVendorNum for frmVendor, and txtJobNum for frmPart. When this is populated, it should save to the corresponding simple table.

[ChipR]

If you are positive that you need the subform...
To save a record, you can use

RunCommand acCmdSaveRecord
'or
If Me.Dirty Then Me.Dirty = False 

I think that to use the first, you would have to call a procedure in the subform's code that would run the command.

[dkotula]

Could I just have qryVendor execute after update of cboRel, and have a criteria set in the query on VendorNum field. The only problem is I don't know how to push the results to the fields in the subform, and match the field name of the query with the name of the field in the subform.

Like:
qryVendor.Name = frmVendor.txtName
qryVendor.Address1 = frmVendor.txtAddress1
qryVendor.Address2 = frmVendor.txtAddress2

If I could have all the fields from everythng on one form, how would I save specific data to individual tables. I think the way I'm doing it now is a little more organized, am I wrong.

I could also unbind the subforms, and have averything run from commands, query and save to table. Wouldn't this option require a ton of code?

I'm so confused, I didn't think this would be such a headache.

Dave

[dkotula]

know what I want to achieve, just don't know how to get there.

[ChipR]

To reference fields in the subform, from the main form, you would use:

Me.subformName.Form!controlName = newValue

Personally, I don't see any reason to use the subform for automatic data entry. They're fine for humans to view or enter records, but if you aren't doing that, you don't need one. I would compose an Insert statement from code on the main form and run it the way I've already indicated.

Regardless, you can't just "run" a Select query from form code, because a Select query returns a recordset. Where would it go? If you need data from other tables to Insert, you have serveral options:

1 - combo box with the recordsource being the other table or a select query
2 - DLookup (Description of DLookup() usage, examples, and troubleshooting)
3 - code to open a recordset and find the correct record.

If you are not looping though this code repeatedly to insert multiple records or something, DLookup is perfectly reasonable. If you are, it could get a little slow.