Refresh Combo Box

[kabing]

I have a form with a Combo Box that I need to refresh after data has been updated in another form. I tried the solution offered in this thread, but when the first form refreshes, I get an error message because an entry is required in the field the combo box relates to.

Thus I would rather use a macro that simply refreshes the combo box in question whenever the mouse clicks on it. More refreshing than I need technically, but it should resolve the problem with refreshing the entire form.

I tried to adapt some code from another macro, and ended up with this:

Code: Select all

sub Refresh_Location_Combobox
aDataForm.getbyname("cbxLocation").refresh()
end sub

But I get an error that I haven't defined a variable. I know almost nothing about macro programming. I'm usually limited to copying code and changing variable names. I can't see here what variable I'm missing or how I need to define it.

Thanks,

kabine

[voobase]

Hi again,

Try this...

Code: Select all

Rem... if the event is coming from the control, i.e a mouse click.
sub Refresh_Location_Combobox (oEv as object)

dim aDataForm as object
aDataForm = oEv.source.model.parent   Rem... oEv.source.model is the control. Add .parent and it is the form (which the control sits in) 

Rem  The line below is going about it in a round about way
aDataForm.getbyname("cbxLocation").refresh()

Rem The following line should do the same exactly the same if the event came from that combobox. Don't forget to REM one of the lines out!
oEv.source.model.refresh()

end sub

Cheers

Voo

[kabing]

I get a "Basic runtime error. Property or method not found."

This happens regardless of whether or not .parent is at the end of aDataForm = oEv.source.model

It also happens regardless of which of the two methods you suggested I use.

kabing

[voobase]

Oh, I remember now.

A combobox does not have a refresh() option. Must be old technology or something.

I think the only known way to reload its list, is with a form reload. I did once ask on the forum about using AllEntriesChanged() method, but no one got back other than to suggest a form.reload, so I assume there is no easy option. Doing a form reload will of course throw the record pointer back to the first record and might take a second or two.

Anyway, your macro for a form reload would be...

Code: Select all

Rem... if the event is coming from the control, i.e a mouse click.
sub Refresh_Location_Combobox (oEv as object)

dim aDataForm as object
aDataForm = oEv.source.model.parent   Rem... oEv.source.model is the control. Add .parent and it is the form (which the control sits in)

        Rem.... Reloads the whole form the combobox is in!
aDataForm.reload()
        
end sub

Also, you may want to have it triggered from a different event than a mouseclick, or even put a separate button on your form. Otherwise your form will be continually reloading as you try to operate the combobox!

Cheers

Voo

ps If anyone knows of a better way, please tell!

[kabing]

Thanks for the explanation. Unfortunately, the form reload is a bit of a problem for me. If I don't already have a location specified in the combo box, I get an error when the form reloads, as that field is a required one. The work around is to type the name of the location in before opening the Locations form to enter a new location.

I'm hesitant to use a listbox, as the list of locations could get very long.

For the moment, then, I'll use the form reload macro attached to the other form.

kabing

[voobase]

Hi again,

Quick suggestion.

Could you instead of trying to refresh the combobox in the other form, use a macro in your "locations" form to check if the combobox field is empty and if so it could copy the location across into the combobox text field (probably with a similar macro to the one you are using to try and do a reload). You could even have a message box pop up to confirm that is what you wanted, if you think that would be needed. This way you would avoid having to do a reload.

I'm hesitant to use a listbox, as the list of locations could get very long.

Are you relying on the combobox feature that the user can start to type the name which would narrow the list in the combobox? Don't forget a combobox may also lead to typo's where a listbox would have the exact name.

A way to avoid the long list could be to use two listbox's in your "field trip" form, in a similar way you did in your "locations" form, to narrow the selections in the "locations" listbox. Perhaps "State" could be in the first listbox.

Regarding reloads. They can get ugly as you may need to set a bookmark, do the reload and return to the bookmark.

Cheers

Voo

[kabing]

Thanks for the suggestions.

Yes, the combo box is so I can start to type and the combo box will auto complete. I am aware of the typo issue, but in this case that's not a concern. The field is required, and it must match a field in the Locations table, so I get an error message when I try to save the data and/or move to the next record.

And I was wrong about my earlier macro reloading the form. I just refreshes the Combo box. Here's the code:

Code: Select all

Sub Refresh_Field_Trips_Data_Entry_Form
Dim tmp    as Object         
   tmp = thisComponent.Parent.FormDocuments
   if tmp.HasByName("FieldTripsInfo") then     
      tmp = tmp.getByName("FieldTripsInfo")
      if not IsNull(tmp.Component) then
         tmp = tmp.Component.DrawPage.Forms.getByName("FieldTripsInfo")
         if tmp.HasByName("cbxLocation") then     
            tmp.getByName("cbxLocation").refresh
         endif
      endif
   endif
End Sub

I do like the idea of altering the macro to drop the correct location name back into the Field Trips form. Are willing to help me figure out how to do that? I would certainly understand if you didn't have time to do so.

kabing

[voobase]

I do like the idea of altering the macro to drop the correct location name back into the Field Trips form. Are willing to help me figure out how to do that? I would certainly understand if you didn't have time to do so.

Yes, sure. I'll give you a hand.

Just checking which exact field would be required to be taken from the "Location" form. I'm guessing it is the "Bound Field" value from the "State" Listbox. If its not we might have to change it slightly.

Give the following code a try. I just tested it out on one of my example databases and it seems to work. You will of course have to substitute your exact form names, and combobox and listbox names etc. To get the code to run you will just need to put the sub's title "Copy_Location_To_Field_Trips_Data_Entry_Form" at the bottom of your "State_Listbox_Copying_and_Reset" sub, so that it copies when ever a selection is made with the "State" listbox. You may also want to put the sub's name at the bottom of the "After_Record_ListBox" sub, or on an event somewhere, depending on how you want it to work.

It's also got a "Yes/No" message box in it. If you don't want that it should be fairly easy to remove.

Code: Select all

       Rem Macro to copy the Boundfield of a listbox in one form to the combobox entry field in another form.
       Rem Trigger from the end of the "State_Listbox_Copy_and_Reset macro and the "After_Record_ListBox" macros could be a good choice.
Sub Refresh_Field_Trips_Data_Entry_Form
Dim tmp    as Object
Dim oForm as object
Dim Location_Name as string

	Rem... This is getting the required data from the boundfield of the second listbox.
oForm = ThisComponent.Drawpage.Forms.getByName("Location_MainForm_Name")
Location_Name = oForm.GetByName("State_Listbox_Name").Boundfield.String
         
   tmp = thisComponent.Parent.FormDocuments
   
   if tmp.HasByName("FieldTripsInfo") then     
      tmp = tmp.getByName("FieldTripsInfo")
      if not IsNull(tmp.Component) then
         tmp = tmp.Component.DrawPage.Forms.getByName("FieldTrips_MainForm_Name")
         if tmp.HasByName("ComboBox_Name") then
         
         	if tmp.GetByName("ComboBox_Name").currentvalue = "" then
	            tmp.getByName("ComboBox_Name").Boundfield.UpdateString(Location_Name)
	        else
		        If MsgBox("The combobox in the ""Field Trip"" form already has something in it. Would you like to exit instead",4,"ComboBox Already Has An Entry!") =6 Then
					Exit Sub
				Else
					tmp.getByName("ComboBox_Name").Boundfield.UpdateString(Location_Name)
				End If
	        end if
	        
         endif
      endif
   endif
End Sub

Let me know how it goes...

Cheers

Voo

[kabing]

It's not the State found field I need copied, it's LocationName. That's a textbox on the Locations form and a combo box on the Field_Trips form.

I didn't use the "State_Listbox_Copying_and_Reset" as I didn't need to copy info to a table grid, and the bound fields were saved to the underlying table without it.

I think for my purposes it would work fine as an "on record change" macro, especially if it would then send me back to the Field Trips data entry form. (Although I can do that manually too).

Would it be easier if you could see a copy of the database? I can make it available for download if that would help.

kabing

[voobase]

Hi Kabing,

Yes it would be nice to have a look. Sounds like an interesting project you have going on.

I think to make it pick up the "Location Name" from the form instead it is a simple change. Just change the line in the macro:

Code: Select all

Location_Name = oForm.GetByName("State_Listbox_Name").Boundfield.String

To:

Code: Select all

Location_Name = oForm.GetByName("txtLocation_Textbox_Name").Text

As for the event that it runs off. I guess you could even have it on the "when loosing focus" event of the Location text box. Guess you need to think what the best operation of the two forms would be.

Yeah love to have a look. Whats the best way to get it to me or are you willing to put it here for download.

[kabing]

I haven't had a chance to play with the last macro yet, but I've sent you a PM with a download link for my database.

kabing

[kabing]

Well, here's how I edited the Macro:

Code: Select all

       Rem... This is getting the required data from the Location Field in the New_Location_Data_Entry field.
    oForm = ThisComponent.Drawpage.Forms.getByName("New_Location_MainForm")
    Location_Name = oForm.GetByName("txtLocationName").Boundfield.String
             
       tmp = thisComponent.Parent.FormDocuments
       
       if tmp.HasByName("FieldTripsInfo") then     
          tmp = tmp.getByName("FieldTripsInfo")
          if not IsNull(tmp.Component) then
             tmp = tmp.Component.DrawPage.Forms.getByName("FieldTripsInfo")
             if tmp.HasByName("cbxLocation") then
             
                if tmp.GetByName("cbxLocation").currentvalue = "" then
                   tmp.getByName("cbxLocation").Boundfield.UpdateString(Location_Name)
               else
                  If MsgBox("The Location combobox in the ""Field Trips Data Entry"" form already has something in it. Would you like to exit instead",4,"ComboBox Already Has An Entry!") =6 Then
                   Exit Sub
                Else
                   tmp.getByName("cbxLocation").Boundfield.UpdateString(Location_Name)
                End If
               end if
              
             endif
          endif
       endif
    End Sub

Note that I opened the New_Location_Data_Entry form for editing and used the Form navigator to change the Mainform name to New_Location_MainForm. All other form and control names should be as you have them in the copy I uploaded.

I attached it to the "On Record Change" event of the New_Location_Data_Entry form. But it doesn't appear to work. In the Field Trips Data Entry form, I entered a Date and a protocol and then clicked on the New Location button. The New_Locations_Data_Entry form opened and I entered the new location and saved. When I returned to the Field Trips Data Entry form, the Location combo box was still empty.

kabing

[voobase]

Hi Kabing,

I'm having a bit of a play with your dB now. I'll show you where I'm up to

I attached it to the "On Record Change" event of the New_Location_Data_Entry form. But it doesn't appear to work.

Yes that event has a trick with it to get it to work. It is explained in the "Forms and Dialogs" pdf by C. Benitez P18 http://www.geocities.com/rbenitez22/ Basically, as the event calls the macro from both the form and the controller the "paths" change so you need an "if" / "else" statement.

I have used a similar event "before record action" which fires when you do a save (the one you have fires whenever you navigate away to another "location" record). The code for this event then calls the (renamed) macro "Copy_Location_Name_To_Field_Trips_Data_Entry_Form". Here is the code.... note: I did not rename "MainForm"

Code: Select all

Sub BeforeRecordAction_Save_Locations (event As Object)

rem ..... As before record action triggers in two different ways this macro is nessicary.
rem .... In this case the macro is called by "before record action"
		Dim Form As Object
		Dim Control As Object
		Dim ControllerName As String
		Dim FormName as String

		ControllerName="com.sun.star.form.FmXFormController"
		FormName = "com.sun.star.comp.forms.ODatabaseForm"

	If Event.Source.ImplementationName=ControllerName Then

		Form=Event.Source.Model
                 Rem MsgBox "FROM FORM CONTROLLER:"
		Rem Just before save completes the Location Name is coppied to Field Trip Form
		If Form.IsNew = True then
Rem This is where it calls the other sub
			Copy_Location_Name_To_Field_Trips_Data_Entry_Form
		End if
	elseIf Event.Source.ImplementationName=FormName Then
Rem
Rem The sub just exits each time it is called from the form. I could have put the code in this section rather than above if wanted to
	exit sub
Rem 	MsgBox "FROM FORM CONTROLLER:
'		Form=Event.Source

   End If

End Sub

With your version the problem would also be with the line that has "boundfield.string". As we are getting the value from a textbox we can just use:

Code: Select all

Location_Name = oForm.GetByName("txtLocationName").Text

The other code is as follows note I have renamed the sub..

Code: Select all

Rem Macro to copy the Boundfield of a listbox in one form to the combobox entry field in another form.
       Rem Trigger from the end of the "State_Listbox_Copy_and_Reset macro and the "After_Record_ListBox" macros could be a good choice.
Sub Copy_Location_Name_To_Field_Trips_Data_Entry_Form
Dim tmp    as Object
Dim oForm as object
Dim Location_Name as string

   Rem... This is getting the required data from the boundfield of the second listbox.
oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
Location_Name = oForm.GetByName("txtLocationName").Text
         msgbox "transferring" & Location_Name
   tmp = thisComponent.Parent.FormDocuments
   
   if tmp.HasByName("Field Trips Data Entry") then     
      tmp = tmp.getByName("Field Trips Data Entry")
      if not IsNull(tmp.Component) then
         tmp = tmp.Component.DrawPage.Forms.getByName("FieldTripsInfo")
         if tmp.HasByName("cbxLocation") then
         
           	if tmp.GetByName("cbxLocation").currentvalue = "" then
               tmp.getByName("cbxLocation").Boundfield.UpdateString(Location_Name)
           	else if tmp.GetByName("cbxLocation").currentvalue <> Location_Name then
	            If MsgBox("The combobox in the ""Field Trip"" form already has something different in it. This form is about to close. Would you like to exit this form without changing that Combobox?",4,"ComboBox Already Has An Entry!") =6 Then
	              	Exit Sub
            	Else
               		tmp.getByName("cbxLocation").Boundfield.UpdateString(Location_Name)
            	End If
            end if
           end if
          
         endif
      endif
   endif
   
End Sub

I have also put a "Save record and exit" button on your location form which does a "save" and closes down the location form. I'm also putting some other tricks in there which I will show you a bit later. I'll PM it back to you in a bit later today hopefully.

I did manage to get your "after record change" macro for the listbox's to work properly. Remember we were having problems with the

Code: Select all

oForm.GetInt(2)

stuff. My fault, I forgot it wasn't an integer we were dealing with but a string so that line should be:

Code: Select all

oForm.GetString(6)

. Here is the whole sub:

Code: Select all

Sub After_Record_Listbox (oEv as object)

   dim oForm as object

      Rem Using the event to get to the form.
      oForm=oEv.Source
     
      Rem Calling limit items again
      Rem You will need to change the number in GetInt(4) to reflect the column number from your data table that holds the "countries" listbox bound field.
      limitItems ( oForm, oForm.GetString(6) )
      oForm.getbyname("lbxState").reset()
 '     msgbox oForm.GetString(6)
End sub

Try it out. You will now see the "state" listbox display something as you change records. (I have gone and changed the "add data only" option of the mainform back to "No" to get back to only using one copy of that form, the "NEW" one).

Anyway to put some of these changes in now by copying them off this post you could.....
1/ Rename the form back to "Mainform"
2/ Copy the (newly named) "Copy_Location_Name_To_Field_Trips_Data_Entry_Form" sub over the other one
3/ Copy into your macro the "Before record action" sub and have it triggered by that "mainform" event from your "locations" form (don't use "before record change")
4/ Copy in the updated "after record change" sub and make sure that the correct event from the mainform is firing it. (don't use "after record action")
5/ Go to your "Mainform" properties and change "add data only" back to "no" so you can check step 4 is doing its job properly.

I'll let you know when I have a bit more.

Cheers

Voo