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 "txtVendorN um 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