473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Help with DLookup

20
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboRelease_AfterUpdate()
  2.     Call Find_WithFilter
  3.     Me.txtJobNum = Me.cboRelease.Column(1)
  4.     Me.txtVendorNum = Me.cboRelease.Column(2)
  5.  
  6. Dim strJobNum As String
  7. strJobNum = DLookup("JobNum", "qryPart", "[JobNum]='" & Me.txtJobNum & "'")
  8. Me!frmPart.Form.txtJobNum = strJobNum
  9.  
  10. Dim strVendorNum As String
  11. strVendorNum = DLookup("VendorNum", "qryVendor", "[VendorNum]=" & Me.txtVendorNum & "")
  12. Me!frmVendor.Form.txtVendorNum = strVendorNum
  13. End Sub
Attached Images
File Type: jpg Form.jpg (8.8 KB, 197 views)
File Type: jpg List.jpg (6.8 KB, 113 views)
Dec 8 '09 #1

✓ answered by ChipR

To reference fields in the subform, from the main form, you would use:
Expand|Select|Wrap|Line Numbers
  1. 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.

23 2724
ChipR
1,287 Expert 1GB
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.
Dec 8 '09 #2
dkotula
20
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.
Dec 8 '09 #3
dkotula
20
ChipR:

In the subform, could I do something like txtJobNum on dirty to query based on information in txtJobNum?
Dec 8 '09 #4
ChipR
1,287 Expert 1GB
Setting the forms to Data Entry means that existing records will not be shown. Have you looked at the Link Master & Link Child fields?
Dec 8 '09 #5
dkotula
20
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.
Dec 8 '09 #6
ChipR
1,287 Expert 1GB
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.
Dec 8 '09 #7
dkotula
20
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT qryPONum.PONUM
  2. FROM qryPONum;
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT qryPONum.POLine
  2. FROM qryPONum
  3. WHERE (((qryPONum.PONUM)=[forms]![frmPO]![cboPO])) ORDER BY qryPONum.POLine;
Expand|Select|Wrap|Line Numbers
  1. SELECT qryPORel.PORelNum, qryPORel.JobNum, qryPORel.VendorNum, qryPORel.POLine, qryPORel.PONum
  2. FROM qryPORel
  3. WHERE (((qryPORel.PONUM)=forms!frmPO!cboPO) And ((qryPORel.POLine)=forms!frmPO!cboLine))
  4. ORDER BY qryPORel.PORelNum;
Dec 8 '09 #8
ChipR
1,287 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. strSQL = "INSERT INTO myTable (field1, field2, field3) " _
  3.  & "Values (" & cboPONum & ", " & cboPOLine & ", " _
  4.  & cboOtherStuff.column(1) & ")"
  5. 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.
Dec 8 '09 #9
dkotula
20
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
Dec 9 '09 #10
ChipR
1,287 Expert 1GB
When you say add additional fields to the table, I read change the table structure. Is this what you mean, or something else?
Dec 9 '09 #11
dkotula
20
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.
Dec 10 '09 #12
ChipR
1,287 Expert 1GB
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.
Dec 10 '09 #13
dkotula
20
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.
Dec 10 '09 #14
dkotula
20
I applied this based on what you provided, and recieved a sytax error INSERT INTO statement.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO tblVendor (Name, Address1, Address2, Address3" _
  3. & "Values (" & txtVendorNum & ")"
  4. DoCmd.RunSQL strSQL
I'm sorry as I'm not real proficient at this.

Dave
Dec 10 '09 #15
ChipR
1,287 Expert 1GB
You may want to insert the line
Expand|Select|Wrap|Line Numbers
  1.  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.
Dec 10 '09 #16
dkotula
20
Wouldn't I need something like this in a SQL statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT "qryVendor.VendorNum,qryVendor.Name, qryVendor.Address1, qryVendor.Address2, qryVendor.Address3" FROM qryVendor _
  2. WHERE ((qryVendor.VendorNum) = Forms!frmPO!txtVendorNum) _
  3. Order BY qryVendor.VendorNum
Dec 10 '09 #17
ChipR
1,287 Expert 1GB
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.
Dec 10 '09 #18
NeoPa
32,556 Expert Mod 16PB
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.
Dec 10 '09 #19
dkotula
20
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.
Dec 10 '09 #20
ChipR
1,287 Expert 1GB
If you are positive that you need the subform...
To save a record, you can use
Expand|Select|Wrap|Line Numbers
  1. RunCommand acCmdSaveRecord
  2. 'or
  3. 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.
Dec 10 '09 #21
dkotula
20
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
Dec 10 '09 #22
dkotula
20
know what I want to achieve, just don't know how to get there.
Dec 10 '09 #23
ChipR
1,287 Expert 1GB
To reference fields in the subform, from the main form, you would use:
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 10 '09 #24

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Kalvin Schroder | last post by:
I am fairly new to Access, and am trying to put together an invoice form. The main form in called InvoiceDetailFm. Source is the table InvoiceDetail and has invoice number, saleman, and CustID as...
0
by: Tony Williams | last post by:
I have posted a number of posts in the Access newsgroups concerning my problem with DLookup. I have had a number of the experts with helpful suggestions but I still can't get it to work! This is...
4
by: basstwo | last post by:
I have a field with a serial number in it. I want to use Mid to extract the 4th and 5th characters, use them to lookup a value on a small lookup table, and use the info from that table to fill in...
5
by: kevinjouco | last post by:
Hello Have searched the group for a solution to the following problem without success: Table 1 has Ref No (No Duplicates) & Min Max Value Fields ie Ref No 1 Min 1 Max 10 Ref No 2 Min 11 Max...
6
by: Don Sealer | last post by:
I've written this expression for a DLookup function. It works almost alright. What I'm trying to do is type in a description and the ID field (number) populates automatically. It works almost as...
6
by: deejayquai | last post by:
Hi I'm attempting to append multiple values into a new record, using multiple criteria from a listbox. I've got the basics for the code below but I get an "Error 3085 Undefined Function" for...
2
by: Don | last post by:
Can someone help me fix my DLookup problem. I'm far from proficiency with Access. I've been creating databases for several years for work with the help of many of you and trial and error. I have...
21
by: Thelma Lubkin | last post by:
I would like my DLookup criteria to say this: Trim(fieldX) = strVar: myVar = _ DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '") I don't believe that this will work, and I...
2
by: Simon | last post by:
Hi, I need some help form someone. I use my database for my online shop, so once I have entered a order onto the database I have a button that creates the Email to the customer to let them know...
1
by: aharding | last post by:
Hello! I am using Access 2003 I have been playing with DLookup all morning and have not been successful yet. I have never used this function...and have some limited experience with code. I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.