UtterAccess Discussion Forums > To Close The Form When We Click The Combo

Full Version: To Close The Form When We Click The Combo

UtterAccess Discussion Forums > Microsoft® Access > Access Reports

Frank Situmorang

Jul 25 2011, 05:25 AM

Hello,

What should I say in VBA of a combo to close the addres form when it is loaded, I put this it seems it does not work.

CODE

If fIsLoaded("ALAMAT PER KELUARGA_JEMAAT KBY") Then
DoCmd.Close acForm, "ALAMAT PER KELUARGA_JEMAAT KBY", acSaveNo
End If

When I click the combo in which event is it, if we press the arrow of the combo, is it on mouse move or key press.

Thanks for any helps.

Frank

Alan_G

Jul 25 2011, 05:40 AM

Hi

I'd imagine from your description the Got Focus or possibly Click would be the event procedures you'd want

Frank Situmorang

Jul 25 2011, 05:43 AM

Thanks Alan, I will give it a try.

Frank

Frank Situmorang

Jul 25 2011, 06:02 PM

Thanks Alan, when I tested it it works perfectly. But one question, the word "acSaveNo" does not mean it will not save the new address data that we just added?, I want it to be saved. Because my intention is in case the user forgot to close the address input form, when they choose it in the member input form it will work.

CODE

DoCmd.Close acForm, "ALAMAT PER KELUARGA_JEMAAT KBY", acSaveNo

Thanks again for your helps

Frank

Alan_G

Jul 25 2011, 06:12 PM

Hi

The constant acSaveNo has no effect at all on whether any data is saved or not, and you could leave it out without any problems and just use

DoCmd.Close acForm, "ALAMAT PER KELUARGA_JEMAAT KBY"

The acSaveNo, acSaveYes and acSavePrompt (from memory I think that's the other constant) constants are used if you make design changes to the form via code, not any data input/edits

Frank Situmorang

Jul 25 2011, 09:09 PM

Thanks Alan, it is clear now to me.

Frank

Alan_G

Jul 26 2011, 06:29 AM

Frank Situmorang

Jul 26 2011, 08:38 AM

Thanks Alan for telling me the on got fokus. I have another question, and I think I want to use also on got focus to enable me to have multi select on the listbox?. This is my VBA in my filter form, when I open it it will automatically go highlighted the default district.

On Got focus or meaning I put my cursor on other district item on the list box, the multislect property will be enabled. How should we say it on the on Lost focus?

CODE

Option Compare Database
Option Explicit

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, IParam As Any) As Long
Private Declare Function GetFocus Lib "user32" () As Long
Private Const WM_VSCROLL = &H115
Private Const SB_THUMBPOSITION = 4

Private Sub Form_Open(Cancel As Integer)

   Dim listSQL As String
   Dim ListRst As DAO.Recordset
   Dim ListDB As DAO.Database
   Dim hWndSB As Long
   Dim lngRet As Long
   Dim lngIndex As Long
   Dim LngThumb As Long

   Me.Org_Bodies.SetFocus
   Set ListDB = CurrentDb
' This query is the same query that the listbox uses.

   listSQL = "SELECT ChurchID, ChurchName_E, ChurchName_L FROM tblChurches ORDER BY ChurchName_L"
   Set ListRst = ListDB.OpenRecordset(listSQL, dbOpenSnapshot)

   With ListRst
        ' This finds the default church ID from the Defaults table.
       .FindFirst "[ChurchID]= " & DLookup("Church", "tblDefaults")

       If Not .NoMatch Then
           lngIndex = .AbsolutePosition
       Else
           lngIndex = 0
       End If
    End With


   'This select the the church in the listbox named Org_Bodies.
  Org_Bodies.Selected(lngIndex) = True
   hWndSB = GetFocus

   LngThumb = MakeDWord(SB_THUMBPOSITION, CInt(lngIndex))
   lngRet = SendMessage(hWndSB, WM_VSCROLL, LngThumb, 0&)

End Sub

'-----------------------------------------------------------------------
Function MakeDWord(loword As Integer, hiword As Integer) As Long
'-----------------------------------------------------------------------
'this is used by scroll in some multi-select lists
    MakeDWord = (hiword * &H10000) Or (loword And &HFFFF&)
End Function

Thanks again for your help.

Frank

Alan_G

Jul 26 2011, 09:52 AM

Hi Frank

Why not just set the Multi Select property of the list box to whatever you want it to be in the control's properties window - in other words just have it as a multi select listbox by default ?

Frank Situmorang

Jul 26 2011, 06:06 PM

Alan,

If I do it, it won't go automatically to the default district items on open event. The reason I make the VBA is to save labor in searching/scrolling down the mouse cursor.

So it should be a property of multiselect to None in the listbox, to be able to work.

Thanks again for your help.

Frank

Alan_G

Jul 26 2011, 07:42 PM

Hi Frank

You can still select an item in a multiselect listbox via code, you just need to do it in a different way. I also don't really see any need for opening a recordset when **I think** you could use a simple DLookup().

So, assuming you set the listbox's multiselect property (in the properties window) to either Simple or Enhanced (whichever one you want to use), try this untested aircode. I've changed the event from the Open to the Load event as well, so that the listbox is populated before the code runs

CODE

Private Sub Form_Load()

Dim lngIndex As Long
Dim lngX As Long

lngIndex = Nz(DLookup("Church", "tblDefaults"),0)

With Me.Org_Bodies
   For lngX = Abs(.ColumnHeads) To .ListCount - 1
      If .ItemData(lngX ) = lngIndex Then
         .Selected(lngX) = True
         Exit For
      End If
   Next lngX
End With

End Sub

I've not included the rest of your code which looks like you're reading/writing to the Registry (?) so if that's needed you'll need to add it back in, and as I said it's untested aircode but something along those lines should be close

Frank Situmorang

Jul 27 2011, 10:34 AM

Alan,

My problem is that the source of Org_Bodies is not the default table, but it is the church table. Only one church to whom I gave the software. In other words we need the default table just to show who is the owner of the software and for the name of each report and form will pick up from this default table.

Thanks for your ideas again.

Frank

Alan_G

Jul 27 2011, 10:51 AM

Hi

Not sure I follow you ?

Going on the code you posted -

"[ChurchID]= " & DLookup("Church", "tblDefaults")

Presumably the DLookup() returns the default ChurchID. In my version I've set a variable to that value

lngIndex = Nz(DLookup("Church", "tblDefaults"),0)

You also say

This query is the same query that the listbox uses.
listSQL = "SELECT ChurchID, ChurchName_E, ChurchName_L FROM tblChurches ORDER BY ChurchName_L"

so I'm taking it that the list box has all of your ChurchID's as the first (probably hidden) column.

All things being equal then, and with the listbox set to MultiSelect, the little loop I posted should select the one that matches the default ChurchID returned by the DLookup() - or have I totally missed something ?

Frank Situmorang

Aug 2 2011, 01:49 AM

Alan,

Our denomination has 5 hirarchies in organization, starting from the lower level
1. Church
2. District
3. Region
4. Union
5. Division

What I want is for District, so we can multi select many districts. In the VBA I made, it is successfully highligehted to the default distirict, if the listbox property made to multiselect to None. Now I want to move from the highligted district to more then one districts. Now my question which event should I make to make the multiselect = True, and what is actually the VBA.

Thanks again for your ideas

Frank

Frank Situmorang

Aug 2 2011, 02:29 AM

Alan,

The idea is to make on the event, on loss focus event?, I want to make it something like this:

CODE

Me.Org_Bodies.Multiselect = True

Do you think it will cancel the Multiselect to None? that I put in the other property of the listbox(Org_Bodies)?

Thanks for your help again.

Frank

Alan_G

Aug 2 2011, 06:18 AM

Hi Frank

Unfortunately the MultiSelect proeprty of a listbox can only be set in form design view, even using VBA code, which more than likely wouldn't be a usable option for what you seem to be wanting to do - particularly so if you're distributing mde/accde versions.

If you want to have the listbox set up as MultiSelect, then I'd say you're going to have to have a rethink and use something like I've outlined above

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.