Select record to display on form

[texasalynn]

I am trying to get a form to select records from what choice is selected. On my form "Log calls..." there are three choices. The first choice lets you select the "SIC" but the record briefly displays then is replaced by the 1st record. Also I want the drop-down to summarize the choices - currently if there are duplicates it shows each one. I wanted the selection to show the summary of all records then when that choice is made it shows only those records. For example: Total records = 50, select choice state=TX, display would then show records = 5 and the first TX record is displayed. I hope this makes sense and someone can help me. I'm just learning VB and don't fully understand it all.

thanks . . . texasalynn

 

[aleb]

As for the display of summarized data in these combos:
SELECT DISTINCT q_Leads.SIC1, Count(q_Leads.SIC1) AS [Number of records]
FROM q_Leads
GROUP BY q_Leads.SIC1;
I think you are actually asking about cascading combo boxes .... if so search this forum on "cascading combo box" ... loooots of topics.




BTW there is some junk in the code of the first combo box ... something like .cmb_sic = null

 

[texasalynn]

I tried putting you suggestion in my code but it doesn't like it. What am I suppose to do with what you gave me?!?!?! (Sorry I'm not the guru you guys are)

And you BTW comment - it's suppose to clear the data from the box once the record is displayed. Of course it does but it also clears the record.

HELP!!! Lost and confused. . . .texasalynn

 

[texasalynn]

Can someone please help? Anyone?

 

[eschaef2]

Hi TexasLynn,

I am in the same boat that you are describing... I'll summarize what I am trying to do, and let me know if it fits your scenario.

I have a table of customer numbers, and the user must select a customer number to work with (the combo box)

After a customer number is selected, the 'form' changes to display the current fields for that customer number (Name, Add1, Add2, etc.)

The user can change any of the data in any field, then save (or cancel) their changes...

-Post back with letting me know if this is what you feel you are doing

 

[stevehooley]

If your users will only be finding records with the combo boxes (rather than scrolling through records using the navigation buttons) I would have the details for each record as a seperate form rather than a subform. In this way the user could:

1) find the record they want with the combo box
2) double click on it or click a GO button
3) the details for the selected record could open in the new form.

I use this method often and I find it seems user friendly and keeps records unlocked as much as possible.

Let me know if this helps...

 

[texasalynn]

Hi TexasLynn,

I am in the same boat that you are describing... I'll summarize what I am trying to do, and let me know if it fits your scenario.

I have a table of customer numbers, and the user must select a customer number to work with (the combo box)

After a customer number is selected, the 'form' changes to display the current fields for that customer number (Name, Add1, Add2, etc.)

The user can change any of the data in any field, then save (or cancel) their changes...

-Post back with letting me know if this is what you feel you are doing

Yes this is what I am looking to do. But a little different in that they will look all customer's within the selected state. So if there are 20 customers in say "TX" I only want those records. SO the navigation buttons would show 20 records. Does that make sense?

 

[razorking]

Your situation sounds somewhat similar to a problem I recently encountered. I was able to get my form functioning to my desires and thought it might be helpful to you.

There are differences between my problem and yours but by dissecting this sample you might get some ideas. I was somewhat confused by your database and problem description (could be my problem as I confuse easily).

Hope this provides some help.

Note: try adding this to the row source for your combobox (from properties):
SELECT DISTINCT [q_Leads].[SIC1] FROM q_Leads;

 

[texasalynn]

Thanks razorking! That helped with the duplicates, but still trying to sort out how to get the records displayed to be all those that are within the choice selected. Right now it brings up the first record for that choice, but if I click on the navagitation button for the next record it isn't related to the choice picked in the combo box. That's what I'm hoping to achieve.

texasalynn

 

[IfButOnly]

You will need to change the forms RecordSource to select only those records matching your combo box selection.

One method is in the query that your form is based on, in criteria of SIC1, put the following to only select records based on the value of your forms combo box or all records if nothing selected in combo box...

LIKE "*" & Forms![frm_Calls].[cboSIC] & "*"

I have a problem with Access at the moment and unable to test it but the code is something like that.

Peter

 

[texasalynn]

Thanks Peter but I couldn't get your suggestion to work. I have made some changes based on suggestions from others and have it working better, but the last part now is getting the selection from the combo boxes to only display the records for that choice.

Private Sub Cmb_Sic_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SIC1] = '" & Me![Cmb_Sic] & "'"
  '  LIKE "*" & Forms![frm_Calls].[Cmb_Sic] & "*"
    Me.Bookmark = rs.Bookmark
   'finds the record of whatever name is selected in the combo box
    Me!USERid.SetFocus
    DoCmd.FindRecord Me!Cmb_Sic
    
    'Set value of combo box equal to an empty string
    Me!Cmb_Sic.Value = Null
End Sub

So if someone could please help with that last piece.

Thanks . . . texasalynn

 

[Pat Hartman]

If the SIC code is numeric, remove the quotes:

rs.FindFirst "[SIC1] = " & Me.[Cmb_Sic]

Take a look at this example I just posted for Cascading Combos