Solved

How to set the value of combo boxes and textboxes equal to null

Posted on 2011-02-25
12
614 Views
Last Modified: 2012-05-11
Hi Experts,

I have a form that is bound to a table in access 2007.  How do I set the values of the form to null until the user makes a selection from the drop-down combo box?  When the user opens the form, the combo box is null, but the other boxes are populated with other information from the first record.  Right now, the user selects from the first combo box on the form and the rest of the form populates with information from that record.  Can someone help me solve this quickly?

 
0
Comment
Question by:daintysally
[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
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984406
if you are adding new record, set the DataEntry property of the form to Yes
or open the form in code in Data entry mode
0
 

Author Comment

by:daintysally
ID: 34984453
I am not adding a new record. I am pulling the record so that the user can make changes to whatever record they select
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984497
ok,
use an Unbound Combo box.
in the afterupdate event of the combo, find the corresponding record

private sub combo1_afterupdate()

with me.recordsetclone
      .findfirst "[Id]=" & me.combo1
      if not .nomatch then
          me.bookmark =.bookmark
      else
         msgbox "record not found"
     end if
end with

end sub



post the rowsource of the combo box...




0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 16

Expert Comment

by:Sheils
ID: 34984539
I believe that what you are trying to do is to use the combo box to search for a record or to use a better term filter through record.

If this is the case then you have to use an unbounded combo box.

Then you can change the recordsource of the form base on what has been selected in the combo box. let me know if that what you want to do and I will send you an example
0
 

Author Comment

by:daintysally
ID: 34984680
Yes, this is exactly what I want to do. I already have the unbound combo box.  I want the user to select an ID from the combo box and when it has been selected, the rest of the form is populated with the information from that record.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984728
see the codes i posted above, just change the name of the field [Id] to actual field name and comob1 with actual name of combo box
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34984773
Here is my code for such an unbound combo box:
Private Sub cboSelect_AfterUpdate()
'Written by Helen Feddema 29-Jun-2009
'Last modified 11-Feb-2010

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34984777
This will not set the values of the controls on the form to Null, however; it will display the values for the selected record.
0
 

Author Comment

by:daintysally
ID: 34984794
Yes, I want to display the values for the selected record.  I just want the user to see a blank textboxes and combo boxes when they first open the form.  Then when they make a selection from the dropdown combo box, everything will display.  Then when the form is closed, all of the values in all of the textboxes and combo boxes are reset to null until another user selects a different 'ID'
0
 
LVL 16

Expert Comment

by:Sheils
ID: 34984823
Use the following code for the onchange even of the unbounded combo box

Private Sub ComboxName_Change()

Me.BoundControlName.SetFocus
DoCmd.FindRecord Me.ComboxName

End Sub

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34984824
set the record source of the form to

select * from tableName
where [id]=Forms!nameofForm!Combo1


now all you have to do now is to requery the form in the after update of the combo box

private sub combo1_afterupdate()

me.requery

end sub
0
 

Author Closing Comment

by:daintysally
ID: 34984874
Thank you everyone for your help!!!  capricorn1 your solution worked perfectly.  I had a macro in the afterupdate event of the combo box and all that I had to do was add 'requery'.  Thank you!!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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