How to refresh form keeping the filled data in controls using macro?

[FelipeAle]

I need to run a Sql command, using a criterion inserted in another control.
.
I have created a macro that performs this. However, when I update the form for the command to be passed, the data filled in the other controls on the form are deleted.
.
How can I do this using a macro while keeping the other controls filled in?
.
Sample code

Private Sub CbbUf_ObterComarca(pEvent)

Dim oCbbUf As Object			' Caixa de combinação UF
Dim oCbbComarca As Object		' Caixa de combinação Comarca
Dim sSql As String				' Comando SQL
Dim sTemp As String				' Texto da cbbUF temporário (Para Windows)
Dim sCriterio As String			' Criterio da Declaração WHERE

	' Comando a ser passado sem criterio
	sSql = "SELECT Comarca FROM tb_Comarcas WHERE UF = "
	
	' Obtem formulário principal para acessar os objetos de desenho
	oForm = pEvent.Source.Model.Parent
	
	' Percorre os controles para obter as Caixas de Combinação Uf e Comarca,
	' e obtem a seleção na cbbUF para filtrar a consulta na cbbComarca
	For Each oCtrl In oForm.ControlModels
		If oCtrl.Name = "cbbUF" Then
			oCbbUf = oCtrl
			sTemp = oCbbUf.Text
			sCriterio = Chr$(39) & sTemp & Chr$(39)		' Criterio deve ser passado com apóstrofes (aspas únicas)
		ElseIf oCtrl.Name = "cbbComarca" Then
			oCbbComarca = oCtrl
		End If
	Next
	
	' Passa o comando Sql com o criterio para a cbbComarca
	oCbbComarca.ListSource = sSql & sCriterio
	
	' Diz ao MainForm que tipo de consulta será passado
	' Ajuda em: https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdb_1_1CommandType.html
	oForm.commandType = 2
	
	' Atualiza o formulário para o comando ser passado com base no SO (Windows = 1; Unix = 4)
	Select Case GetGuiType()
		Case 1
			oForm.reload()
			oCbbUf.Text = sTemp
		Case 4	
			oForm.reload()
	End Select
	
End Sub 'BasicProject.Arquivos.CbbUf_ObterComarca

.
Sample File

Ask_OficioDB.odb

[cpb]

you are using a relational database (HSQL embedded).
even a small database requires careful pre-planning with the focus on structure and normalisation.
you will not achieve success using your current strategy.

you have 3 tables containing source data (that’s good).
you wish to select a mix of values from those 3 tables, add a few other literal values, and then save to a 4th table.

you use 3 combo boxes why?
if you type a new value into a combo then that value which should be stored in the source table is actually stored in the destination table.
all of your tables should have auto-incrementing primary keys and you should be using LIST BOXES.
we should be using foreign keys to link tables which in turn helps ensure data integrity.

your question is:
I have over 300 records which fall into a number of categories(UF), how can I limit the number of possible selections to 1 particular category?

it’s easy to limit selection using sql (as you are trying to do) or a filter table but neither is practical here.
I suggest you try a list box concatenating the category with description e.g. (SP - Bilac).
it’s easy and quick to navigate a list box:
use the slider or hit home, end, pageup, pagedown, arrow keys.
quickly type SP to choose from SP’s or hit T to choose from the T’s.

download the attachment to give it a try.
no macros necessary, I gave the tables auto-incrementing primary keys and changed your form a little as required.
Oficio.odb (32.4 KB)

[FelipeAle]

@cpb

you wish to select a mix of values from those 3 tables, add a few other literal values, and then save to a 4th table.

Yes.
.

@cpb

I have over 300 records which fall into a number of categories(UF), how can I limit the number of possible selections to 1 particular category?

Exactly.

@cpb

it’s easy to limit selection using sql (as you are trying to do) or a filter table but neither is practical here.

I’m still learning about the SQL Language, so I did not realize that I could do what I wanted using a better structured query.
.
Thanks a lot for the hints and tips!