Yet another combo box problem 2

[RacerGirl117]

I have a main form that has 3 subforms. The subforms are on a tab control. The main form and the first two subforms are set as Snapshot so the user cannot change the data. The 3rd subform is a data entry form with 5 fields, two of them being combo boxes.

The 2nd combo box (Job No.) on this subform has two column and is based on an existing query. When a users selects a value from this combo box it is stored in the field. This combo box works wonderfully.

The 1st combo box (Svc. Addr. ID) has three columns and is based on a query I created specifically for this subform because of the value I need to store in this field. In the original table where the value comes from it is 12 digits long. I only want to display the 3 digits furthest on the right hand side, so in the query I have formatted it as follows:
SvcAddID: Right([SV_AD_ADDR_ID],3)
This makes the length of the "original" field and the length in the subform the same.

The columns in this combo box are as follows:
Cust No
SvcAdrID
SV_ADR_NAME

I have the BoundColumn property of the combo box set to 2 and LimittoList set to Yes. Yet, when I open the form and select a value from the list, no matter what value I select, it shows the first line.

I don't know what else to try with this thing. Am I missing something here? Any suggestions would be greatly appreciated. Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[CosmoKramer]

Jessica,

Just for the heck of it, check the Column Count property for the combo box. Make sure its the correct number of columns from your query.

Something like this happened to me once where I changed the number of columns in the record source of a combo box, but I didn't change the column count property.....

 

[RacerGirl117]

Column count is correct - 3.

What I've done since this post is set the criteria in the Cust No field of the query to match the Cust No field of the main form and deselected the "Show" box in the query. That narrows the selection down a bit. Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[Nyanzi]

Jessica,

I need some help from you. In your text, you said you have a combo box based on a query. When a user makes a selection in this combo box the value is stored in a table. How did you do this?

 

[RacerGirl117]

Nyanzi,

The combo box field on this subform is bound to a table. The table this subform is based on is a "stand-alone" table and is merely used to store data.

I'm not quite sure what you're asking, so I hope this helps.

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[judgehopkins]

To the people in this thread: I am able to delete entries from a combobox using a form and a query. An expert showed me this method and it works great!

'Here is what I put on the delete county form
'
' Notes:
' ---
' 1) The code created was in response to a newsgroup posting
' 2) The underscore character used in the code is a line-continuation character
'

Private Sub cmdCancel_Click()

  On Error GoTo Proc_Err
  
  DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
  
Proc_Exit:
  Exit Sub

Proc_Err:
  MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
  Resume Proc_Exit
  
End Sub

Private Sub cmdOK_Click()

  On Error GoTo Proc_Err
  
  If IsNull(Me.cboCounties) Then
    
    ' The user clicked OK without selecting an entry
    '
    MsgBox _
      Prompt:="A selection must be made before proceeding.", _
      Buttons:=vbInformation + vbOKOnly
    Me.cboCounties.SetFocus
  
  Else
  
    ' The user made a selection. Display a "second chance" prompt to be sure
    ' the user wants to delete the entry
    '
    If vbYes = MsgBox _
      ( _
        Prompt:="You have selected a county to be deleted." & vbCrLf & "Are you sure?", _
        Buttons:=vbExclamation + vbYesNo + vbDefaultButton2 _
      ) Then
    
        ' The user wants to delete the entry
        If DeleteEntry(Me.cboCounties) Then
          MsgBox _
            Prompt:="The selected county was successfuly deleted.", _
            Buttons:=vbInformation + vbOKOnly
          
          ' Now that the entry was deleted, requery the combo box so that
          ' the entry is removed from the list. Otherwise, Access gives a
          ' weird result in the place where the county existed.
          '
          Me.cboCounties.Requery
          
          ' Clear the combo box so another entry can be selected.
          Me.cboCounties = Null
          
        Else
          MsgBox _
            Prompt:="The selected county could not be deleted.", _
            Buttons:=vbExclamation + vbOKOnly
        End If
    
    End If
  
  End If
  
Proc_Exit:
  Exit Sub

Proc_Err:
  MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
  Resume Proc_Exit

End Sub

Private Function DeleteEntry( _
  pstrEntry As String) As Boolean

'
' Comments: This will delete the County entry that was selected.
'
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim fRetVal As Boolean

  On Error GoTo Proc_Err

  ' This is how you execute a query programmatically and how
  ' you pass it a parameter.
  '
  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs("qryDeleteCountyEntry")
  qdf![prmCounty] = pstrEntry
  qdf.Execute
  
  ' Return a TRUE value to indicate the process completed successfully
  '
  fRetVal = True

Proc_Exit:
  Set qdf = Nothing
  Set dbs = Nothing
  DeleteEntry = fRetVal
  Exit Function

Proc_Err:
  fRetVal = False
  MsgBox Prompt:=Err.Description, Buttons:=vbExclamation
  Resume Proc_Exit

End Function

'Here is the procedure I use on the Order form
Private Sub Delete_a_County_Click()
On Error GoTo Err_Delete_a_County_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmDeleteCounty"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Delete_a_County_Click:
    Exit Sub

Err_Delete_a_County_Click:
    MsgBox Err.Description
    Resume Exit_Delete_a_County_Click
    
End Sub

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath