Solved

access 2007 combobox won't requery

Posted on 2013-01-25
3
1,595 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

691 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