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 |
#1
|
|||
|
|||
Somebody Please Help Me
What kind of a database cannot retrieve a record and show it on a form?
Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#2
|
|||
|
|||
Somebody Please Help Me
oldblindpew,
You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#3
|
|||
|
|||
Somebody Please Help Me
Gina,
Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#4
|
|||
|
|||
Somebody Please Help Me
oldblindpew,
Then why not add the key and make it invisible? It doesn't have to show to work... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... Gina, Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#5
|
|||
|
|||
Somebody Please Help Me
I'm not sure I'm following allo of this, but you can reference any column in
a combo box or list box row source: Me.lstFirm.Column(1) references the second column in the list box (numbering is zero-based in this case). If you simply reference the list box you reference the bound column. There is no need to change the bound column. I am not familiar with GoToRecord. When I use an unbound combo box (or list box) to search, its After Update code I use is something like this: Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[FirmID] = " & Me.lstFirm Me.Bookmark = rs.Bookmark This assumes FirmID is a number field (including autonumber). If you search by firm name and there are duplicates you may not end up at the record you want no matter the method. All of this assumes the list box row source Gina described (two columns, with the unique number field in the first column and the firm name in the second). BTW, the first column is 1 on the Property Sheet, but it is 0 in code. As described, a reference to the list box references the bound column. Me.lstFirm references the bound column. You can refer to any field in a form's record source, but until you go to the record you don't have that option. That is why the reference To move the cursor to a control (not a field, which is in a table or query): Me.ControlName.SetFocus You refer to moving the cursor first to a "field", then to the command button in the same code. You can move the focus, but of course the command button won't accept a cursor, unless I misunderstand something in what you are saying. Just as an observation, your frustration is apparent and in some ways understandable, but many developers have achieved very good results with Access databases. There is a rather steep learning curve, to be sure, but there is also a lot of free information and many sample databases to help guide the learning process. Your approach to this problem has been somewhat unconventional, so it is hard to see how you have come to the conclusion that Access is "lame". Some people may perceive that as a chip on your shoulder, and be reluctant to attempt a reply for that reason. There are some good articles and tips on this page: http://allenbrowne.com/tips.html On the right side of the page are links to many more sites. "oldblindpew" wrote in message ... Gina, Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#6
|
|||
|
|||
Somebody Please Help Me
Gina Whipp,
I had considered trying what you suggest, but just didn't believe such a goofy contrivance should be necessary. However, in desperation I finally did try it, and guess what? Microsoft Access cannot move to a hidden control! In a way I'm glad about this because I don't think is would make sense to be able to move the cursor to a hidden control. I made the control visible, and now everything works perfectly. Except I do not want to have to see the Firm ID!!! This is so crazy...when I originally built the Firms table, I devised a key field based on an alphabetical naming convention to uniquely identify each firm. In time, however, I came to accept what I read about how much better it is to let Access take care of the key field via autonumbering, so I got rid of my text-based key and replaced it with an autonumber key. Thus I went to the trouble of switching to autonumber, but am not really gaining the benefit from it!! From my point of view, I keep throwing easy pitches, and Access keeps striking out. I know there are answers out there, but an answer you can't find and use and master with reasonable time and effort isn't an answer at all. "Gina Whipp" wrote: oldblindpew, Then why not add the key and make it invisible? It doesn't have to show to work... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... Gina, Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#7
|
|||
|
|||
Somebody Please Help Me
Why are you trying to move the cursor to a hidden control... Just make it
Tab Stop = No and make no refrence to move ot it, it is not neccessary. Does this database have any data? I'd be happy to take a look for FREE, as this should work, before you go crazy and not after! -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... Gina Whipp, I had considered trying what you suggest, but just didn't believe such a goofy contrivance should be necessary. However, in desperation I finally did try it, and guess what? Microsoft Access cannot move to a hidden control! In a way I'm glad about this because I don't think is would make sense to be able to move the cursor to a hidden control. I made the control visible, and now everything works perfectly. Except I do not want to have to see the Firm ID!!! This is so crazy...when I originally built the Firms table, I devised a key field based on an alphabetical naming convention to uniquely identify each firm. In time, however, I came to accept what I read about how much better it is to let Access take care of the key field via autonumbering, so I got rid of my text-based key and replaced it with an autonumber key. Thus I went to the trouble of switching to autonumber, but am not really gaining the benefit from it!! From my point of view, I keep throwing easy pitches, and Access keeps striking out. I know there are answers out there, but an answer you can't find and use and master with reasonable time and effort isn't an answer at all. "Gina Whipp" wrote: oldblindpew, Then why not add the key and make it invisible? It doesn't have to show to work... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... Gina, Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#8
|
|||
|
|||
Somebody Please Help Me
Gina,
Thanks for your patience and generosity. Let me say, again, the vital bit of code here is DoCmd.FindRecord. Now, I'm not saying this is the best command to accomplish my task, but it just seemed to me the most likely candidate. This command, or method, seems to depend for its operation on the control in the form that has focus. I thought I had made this quite clear in my prior posts. FindRecord has to know which column (field) to search, and the way it knows is by the control in the form that has focus, which is bound to the appropriate field in the table. If the correct contol does not have focus, FindRecord is looking in the wrong column and therefore does not find a match on the value it was sent in search of. This is why the cursor has to be moved to the right control (text box) before using FindRecord. Therefore if I want to use FindRecord to search for a Firm by Firm ID, I must have that field visibly represented on my form, and it must have focus. "Gina Whipp" wrote: Why are you trying to move the cursor to a hidden control... Just make it Tab Stop = No and make no refrence to move ot it, it is not neccessary. Does this database have any data? I'd be happy to take a look for FREE, as this should work, before you go crazy and not after! -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... Gina Whipp, I had considered trying what you suggest, but just didn't believe such a goofy contrivance should be necessary. However, in desperation I finally did try it, and guess what? Microsoft Access cannot move to a hidden control! In a way I'm glad about this because I don't think is would make sense to be able to move the cursor to a hidden control. I made the control visible, and now everything works perfectly. Except I do not want to have to see the Firm ID!!! This is so crazy...when I originally built the Firms table, I devised a key field based on an alphabetical naming convention to uniquely identify each firm. In time, however, I came to accept what I read about how much better it is to let Access take care of the key field via autonumbering, so I got rid of my text-based key and replaced it with an autonumber key. Thus I went to the trouble of switching to autonumber, but am not really gaining the benefit from it!! From my point of view, I keep throwing easy pitches, and Access keeps striking out. I know there are answers out there, but an answer you can't find and use and master with reasonable time and effort isn't an answer at all. "Gina Whipp" wrote: oldblindpew, Then why not add the key and make it invisible? It doesn't have to show to work... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... Gina, Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#9
|
|||
|
|||
Somebody Please Help Me
Access does not rely on "goofy contrivances", but rather on procedures and
methods with which you may not be familiar. You seem to think Access "should" do things the way you want rather than the way it is designed to work. There certainly are some things about Access that need improvement, but it is well able to do what I think you have described. For one thing, as Gina said there is no reason to move out of the control. I outlined in another post in this thread some ways of doing what I think you are trying to do. "oldblindpew" wrote in message ... Gina Whipp, I had considered trying what you suggest, but just didn't believe such a goofy contrivance should be necessary. However, in desperation I finally did try it, and guess what? Microsoft Access cannot move to a hidden control! In a way I'm glad about this because I don't think is would make sense to be able to move the cursor to a hidden control. I made the control visible, and now everything works perfectly. Except I do not want to have to see the Firm ID!!! This is so crazy...when I originally built the Firms table, I devised a key field based on an alphabetical naming convention to uniquely identify each firm. In time, however, I came to accept what I read about how much better it is to let Access take care of the key field via autonumbering, so I got rid of my text-based key and replaced it with an autonumber key. Thus I went to the trouble of switching to autonumber, but am not really gaining the benefit from it!! From my point of view, I keep throwing easy pitches, and Access keeps striking out. I know there are answers out there, but an answer you can't find and use and master with reasonable time and effort isn't an answer at all. "Gina Whipp" wrote: oldblindpew, Then why not add the key and make it invisible? It doesn't have to show to work... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... Gina, Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
#10
|
|||
|
|||
Somebody Please Help Me
BruceM,
Thanks for your very kind response. I am not familiar with GoToRecord either. As stated, I only tried it because I couldn't get FindRecord to work. I don't doubt that my approach has been unconventional, but that is only because I do not know what the conventional approach is. I developed my application largely by copying and modifying stuff from sample databases. I have two books, one is Access 200 Programming from the Ground Up, by Whil Hentzen, which I assure you is very well-thumbed, and more recently, Access 2007 VBA Programmer's Reference, which is a bit over my head, and not helped by the numerous typos I keep finding. In addition, there is of course the sometimes-helpful Microsoft F1 Help. Despite all this I have not really come upon a "conventional" method for finding a record and displaying it to a form, which is pretty stunning since that is what databases are supposed to do. I am only just now beginning to get the impression that the conventional answer may involve Cloned RecordSets and Bookmarks, but these are completely new concepts to me and sound over-complicated for the simple task I'm trying to accomplish. This morning I have gained some ground by abandoning FindRecord in favor of Seek. Could you tell me, once I've found the desired record via Seek, how to make that record the current record so it appears on the form? Thanks "BruceM" wrote: I'm not sure I'm following allo of this, but you can reference any column in a combo box or list box row source: Me.lstFirm.Column(1) references the second column in the list box (numbering is zero-based in this case). If you simply reference the list box you reference the bound column. There is no need to change the bound column. I am not familiar with GoToRecord. When I use an unbound combo box (or list box) to search, its After Update code I use is something like this: Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[FirmID] = " & Me.lstFirm Me.Bookmark = rs.Bookmark This assumes FirmID is a number field (including autonumber). If you search by firm name and there are duplicates you may not end up at the record you want no matter the method. All of this assumes the list box row source Gina described (two columns, with the unique number field in the first column and the firm name in the second). BTW, the first column is 1 on the Property Sheet, but it is 0 in code. As described, a reference to the list box references the bound column. Me.lstFirm references the bound column. You can refer to any field in a form's record source, but until you go to the record you don't have that option. That is why the reference To move the cursor to a control (not a field, which is in a table or query): Me.ControlName.SetFocus You refer to moving the cursor first to a "field", then to the command button in the same code. You can move the focus, but of course the command button won't accept a cursor, unless I misunderstand something in what you are saying. Just as an observation, your frustration is apparent and in some ways understandable, but many developers have achieved very good results with Access databases. There is a rather steep learning curve, to be sure, but there is also a lot of free information and many sample databases to help guide the learning process. Your approach to this problem has been somewhat unconventional, so it is hard to see how you have come to the conclusion that Access is "lame". Some people may perceive that as a chip on your shoulder, and be reluctant to attempt a reply for that reason. There are some good articles and tips on this page: http://allenbrowne.com/tips.html On the right side of the page are links to many more sites. "oldblindpew" wrote in message ... Gina, Thanks for replying. My list box is already set up as you describe. When I wrote of passing either the Firm Name to a sting variable or conversely, the Firm ID to a long integer variable, this involved changing the bound column to either 2 or 1, respectively. I think the problem has to do with the fact that my Firms maintenance form is focussed on the Firm Name. DoCmd.FindRecord seems unable to search the Firm ID field, but only the Firm Name field, due to this implied focus. If I were to add a Firm ID text box to my maintenance form, and then go to that control prior to running DoCmd.FindRecord, I have no doubt it would work. But the whole idea of having a surrogate autonumbering key is that it should lurk in the background without the user having to see it or know about it. Another solution would be to alter the names of firms to eliminate any duplicates, but monkeying with the names just to accomodate the lameness of the database system is unacceptable. In essence, this would change the Firm Name into the real key field. If so, why have a surrogate key? I'm sure there is an answer here somewhere; I can't be the first person to want to do this. I'm just amazed that finding a way to perform so basic a database operation could ever be this difficult and time-consuming. "Gina Whipp" wrote: oldblindpew, You would need to use the Firm's unique ID and use the same thing in the list box, which ight be why it didn't work before because of the way the list box was set up. Try setting up the list box as 2 columns and make the first one 0" width but set the bound column as 1. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... What kind of a database cannot retrieve a record and show it on a form? Here's the flow: The user opens a form for Firms maintenance. On this form is a textbox which displays the name of the firm. Next to this textbox is a command button with a picture of binoculars on it. When you click on the button, a dialog form is opened containing a list box showing all the firms in alphabetical order. The user finds the firm he is interested in, and selects it. At this point the dialog box closes and the selected firm's record appears on the underlying form, but this last bit doesn't work for duplicate firm names. Access retrieves only the first instance of the selected firm, regardless of which instance the user selects. Here is the code behind the event: Option Compare Database 'Use database order for string comparisons. Option Explicit 'Requires variables be declared before use. Private Sub cmdOkFindFirm_Click() ' Find record for main form based on selection in dialog form. On Error GoTo Err_OkFindFirm ' Check to see if no selection was made. If IsNull(Me!lstFirm) Then MsgBox "Make a selection or click Cancel", , "No Selection" GoTo Exit_OkFindFirm End If ' Store the selection in a variable. Dim lngSelect As Long lngSelect = Me!lstFirm 'Dim strSelect As String 'strSelect = Me!lstFirm ' Close the dialog form to switch back to the main form. DoCmd.Close ' Move the cursor back to the search field. ' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't. DoCmd.GoToControl "txbFirmName" ' Find the selected record. 'DoCmd.FindRecord lngSelect, , , , , acAll 'DoCmd.FindRecord strSelect DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect ' Move the cursor back to the search button. DoCmd.GoToControl "cmdSearchFirm" Exit_OkFindFirm: On Error Resume Next Exit Sub Err_OkFindFirm: MsgBox "Error #: " & Err & Chr(13) & Err.Description Resume Exit_OkFindFirm End Sub Originally, I put the firm's name into a string variable (strSelect) and did DoCmd.FindRecord strSelect. This works, but not for duplicate firm names. Next I tried passing the firm's unique ID number to a long integer variable. This did not work at all, doubtless because FindRecord was searching the firm name field for the firm ID number, which of course isn't there. Next, I tried to force FindRecord to look in all fields for a match to the firm ID. I didn't much care for this approach, but it didn't work anyway, so that was okay. The most recent thing I tried was passing the firm Id number to DoCmd.GoToRecord. This gave me an error message stating that the Firms table wasn't open. My brain hurts. |
Thread Tools | |
Display Modes | |
|
|