Solved

access 2007 combobox won't requery

Posted on 2013-01-25
3
1,581 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now