Solved

access 2007 combobox won't requery

Posted on 2013-01-25
3
1,588 Views
Last Modified: 2013-01-25
I have a combobox that pulls a list of techs from my table.  This works (thanks to boag2000).  I have two questions one of which is not concerning requery.

1) what is the best way to input a "blank" into the combobox as the first entry the user will see when the form opens?

2) I have tried both of these to get the combo box to requery and nothing appears to be happening.  

Forms![techs]![cmbID].Requery

Forms("techs").Controls("cmbID").Requery

If my user selects a tech from the combobox and then opens the delete tech form but decides to just close the form the requery I think should set the combobox and any textboxes (these are linked to the combobox) back to blank but it doesn't.  What am I doing wrong.  One last item, is there a better way to delete records from the various tables than what I have listed below?

Private Sub cmdCForm_LostFocus()
   
    'Forms![techs]![cmbID].Requery
    Forms("techs").Controls("cmbid").Requery
   

End Sub

Private Sub cmdDTech_Click()
Dim intAnswer As Integer
Dim strsql As String

    If Me.NewRecord Then
        Me.Undo
        Exit Sub
    End If
   
    intAnswer = MsgBox("Are you sure you want to delete this tech " _
      & " and their records?", vbQuestion + vbYesNo, "Tech deleted")
     
    If intAnswer = vbNo Then
        Exit Sub
    End If
   
    'delete records
    strsql = "delete * from ctused" & " where id = " & Me.txtID
    CurrentProject.Connection.Execute strsql
   
    strsql = "delete * from ctbal" & " where id = " & Me.txtID
    CurrentProject.Connection.Execute strsql
   
    strsql = "delete * from ctsubmitted" & " where id = " & Me.txtID
    CurrentProject.Connection.Execute strsql
   
    'delete tech from tech table
    strsql = "delete * from techs " & " where ID = " & Me.txtID
    CurrentProject.Connection.Execute strsql
     
End Sub
0
Comment
Question by:sptech
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38817867
I don't really follow what is going on.

Is this an unbound combobox?
If so, what do you see when the form is opened.  I wouldn't  expect to see any value selected, unless you have actively programmed that to happen.

To set an unbound combo to 'empty' just do

Forms!formname!comboname = Null
0
 

Author Comment

by:sptech
ID: 38817967
The combo box is populated from a select query in the row source property of the combobox.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38818181
<<
 requery I think should set the combobox and any textboxes (these are linked to the combobox) back to blank but it doesn't
>>

Requery doesn't work quite the way you think it does.  

For a combo box, think in terms of it's list being selected from a table or query.  If data in that table changes, the list shown in the combo box will not automatically show that change.

Me.myCombo.Requery will refresh the list and reflect any changes that have been made to the underlying data in your table or changes to the criteria of a query.

<< back to blank >>

Pete's solution will 'clear out' the textbox portion of your combo box (set it to blank), and as he explained the default entry in your combo box when the form initially opens is blank/null, unless you have set the  Default Value property to display something else or are otherwise changing it through code.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question