If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Making one field on a form dependent on another fields results
If the query's not returning any rows, that would explain why you're not
getting anything in your combobox. You sure you've got records with [Facility Location] = '4'? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ETC" wrote in message ... I made the changes. Did another Ctrl+G, pasted the line into SQL view and ran it. It didn't display any values at all...just the field heading. "Douglas J Steele" wrote: The following properties should be changed for cboPk_ID: Row Source: leave it blank (you're setting it later in code) Column Count: 1 Column Widths: 1" Okay, take that SQL you got from the Immediate window (SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility Location] = '4') and copy it into the clipboard. Create a new query, and don't select any tables from the list that appears. Select SQL View from the View menu, and paste the SQL into the window that appears. Click on the Exclamation Point button on the button bar to run the query (or select Query | Run from the menu). Does the SQL return the appropriate information? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ETC" wrote in message news So, what exactly should I change in the property for which combo box? Also, when I hit Ctrl+G I get the following... SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility Location] = '4' "Douglas J. Steele" wrote: The details for cboPk_ID are inappropriate for what you set its RowSource to in cboFacLoc_AfterUpdate: it's saying 2 columns, while your row source is changed to SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] ..., which only has one column. That may well be the issue. The Debug statement is supposed to print the actual SQL you're trying to use in the Immediate window. Use Ctrl-G to go into the VB Editor once you've selected a value in cboFacLoc, and you should see the SQL printed out in the window in the bottom right. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ETC" wrote in message ... Properties... Name: cboFacLoc Control Source: Facility Location Decimal Places: Auto Row Source Type: Table/Query Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].[Facility Location] FROM [Lawson Codes]; Column Count: 2 Colmn Heads: No Colmn Widths 0";1" Bound Column: 1 List Rows: 8 List Width: 1" Limit to List: Yes Auto Expand: Yes IME Hold: No IME Mode: No Control IME Sentence Mod: None Visible: Yes Display When: Always Enabled: Yes Locked: No Allow AutoCorrect: Yes Tab Stop: Yes Tab Index: 0 Left: 0.9583" Top: 0.125" Width: 0.7083" Height: 0.1771" Back Style: Normal Back Color: -2147483643 Special Effect: Sunken Border Style: Solid Border Color: 0 Border Width: Hairline Fore Color: -2147483640 Help Context Id: 0 After Update: Private Sub cboFacLoc_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCT [Lawson Codes].Pk_ID " & _ "FROM [Lawson Codes] " & _ "WHERE [Facility Location] = '" & Me!cboFacLoc & "'" Debug.Print strSQL Me.cboPk_ID.RowSource = strSQL Me.cboPk_ID.Requery End Sub Reading Order: Context Keyborad Language: Shystem Scroll Bar align: System Numeral Shapes: System Is Hyperlink: No The other combo box... Name: cboPk_ID Control Source: Pk_ID Decimal Places: Auto Row Source Type: Table/Query Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].Pk_ID FROM [Lawson Codes]; Column Count: 2 Column Heads: No Column Widths: 0";1" Bound Column: 1 List Rows: 8 List Width: 1" Limit To List: Yes Auto Expand: Yes IME Hold: No IME Mode: No Control IME Sentence Mode: None Visible: Yes Display When: Always Enabled: Yes Locked: No Allow AutoCorrect: Yes Tab Stop: Yes Tab Index: 1 Help Context Id: 0 Reading Order: Context Keyboard Language: System Scroll Bar Align: System Numeral Shapes: System Is Hyperlink: No I left out the font and border properties (I didn't think you cared to know about those). All other fields are blank in these two combo boxes. As you can see I put in the Debug.Print in the AfterUpdate Event procedure, but I'm not understanding what it's supposed to do. "Douglas J Steele" wrote: What are the properties for your combobox? And one test to try. Add a Debug.Print to your AfterUpdate routine, to write the SQL statement out to the Debug window (Ctrl-G): Private Sub cboFacLoc_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCT [L Codes].X_ID " & _ "FROM [L Codes] " & _ "WHERE [Fac Loc] = '" & Me!cboFacLoc & "'" Debug.Print strSQL Me.cboX_ID.RowSource = strSQL Me.cboX_ID.Requery End Sub Copy what's printed out for strSQL in the Debug window and paste it into a new query. Does the query run properly? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ETC" wrote in message ... Doug, I am SO sorry, but it's just not working. The cboPk_ID combo box is still showing up blank. I think I'll just resort to the uglier, "more-complicated-for-the-user" way of doing it. But I thank you for your time. ETC "Douglas J Steele" wrote: You'll never get a list of unique X_ID values if you include No as well. And since you know what Fac Loc value you're looking at, there's really no need to include it in the query. The fact that Fac Loc is a text value means you need to include quotes around the value you're passing: Private Sub cboFacLoc_AfterUpdate() Dim strSQL As String strSQL = "SELECT DISTINCT [L Codes].X_ID " & _ "FROM [L Codes] " & _ "WHERE [Fac Loc] = '" & Me!cboFacLoc & "'" Me.cboX_ID.RowSource = strSQL Me.cboX_ID.Requery End Sub Exagerated for clarity, strSQL is: strSQL = "SELECT DISTINCT [L Codes].X_ID " & _ "FROM [L Codes] " & _ "WHERE [Fac Loc] = ' " & Me!cboFacLoc & " ' " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ETC" wrote in message ... Yes, I have a field named No in my table. It's the primary key. If I set the Row Source Type property set to Table/Query, then I don't know how to keep objects from repeating on the dropdown list. But here, let me try this again... My L Codes Table looks like: No Fac Loc X_ID X Type L Code Price 1 A P Y 1 $5.00 2 A P Z 3 $10.00 3 A Q Y 2 $20.00 4 A Q Z 3 $10.00 5 B R Y 1 $5.00 6 B R Z 3 $10.00 7 B S Y 2 $20.00 8 C S Z 2 $20.00 9 C T Y 3 $10.00 10 C T Z 4 $30.00 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pass Form Text Input Field to Separate Form Input Field | Robert Nusz @ DPS | Using Forms | 3 | December 21st, 2004 11:53 PM |
How to get a field on a form to reflect a certain record of a query? | General Discussion | 0 | December 11th, 2004 12:56 AM | |
dlookup | miaplacidus | Using Forms | 9 | August 5th, 2004 09:16 PM |
auto entry into second table after update | Tony | New Users | 13 | July 9th, 2004 10:42 PM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |