Urgent help with Cascading Combo

[sub_nitrox]

Hello to everybody!
I need a big help to solve a problem with my DB Access.
I created a form in a DB where I have to fill some information and store them in a table.
I created the cascading combo box. It works, infact when I select the value in the first Combo box named NOME_PROVIDER the second combo named NUM_MAM comes populated with the value got from a query. Anyhow it works well.

THE PROBLEM right now is that I can only select the first value of NOME_PROVIDER and not all the other. I'm sure that the selection query works, but I'm not able to select the other values. Maybe could be a focus problem or something like that.

PLEASE CAn you give me an help on this matter?
Thanks in advance to everybody!!!
Cheers,
Antonio.

 

[sub_nitrox]

HI again to everybode.
To better explain the problem, please find attached a section of my DB where i slocated the problem.

Regards,
Antonio.

 

[CraigDolphin]

Antonio,

the form you posted has some issues. Some of which arise from you renaming combo boxes on the form without making the appropriate changes in your vba.

The reason you can't select anything else in your cboNOME_PROVIDER is that you have code in your after_update event that first deletes your entry, then requeries your dropdown list in the same combo box, then sets the value in the combobox back to the first item in the list.
The following is completely superfluous:

Me.cboNOME_PROVIDER = Null
Me.cboNOME_PROVIDER.Requery
Me.cboNOME_PROVIDER = Me.cboNOME_PROVIDER.ItemData(0)
Me.NUM_MAM1.SetFocus
Me.NUM_MAM1.Dropdown

Instead, all you need do is place the single line in the after_update event of your cboNOME_PROVIDER control:

Me.NUM_MAM1.Requery

Since the row source for your NUM_MAM1 Combo box already uses the value in the cboNOME_PROVIDER combobox, you only need to requery it once you have selected your NOME_PROVIDER.

You might also want to use the On_Enter event of NUM_MAM1 to make sure that cboNOME_PROVIDER is not null to prevent them selecting values from this control without first specifying their NOME_PROVIDER. Or, alternatively, use the onopen event of your form to set the focus to your cboNOME_PROVIDER control, and use the lost focus event to prevent the user moving anywhere else in the form until they select a NOME_PROVIDER

First though, you need to spend time cleaning up your code to delete events and references that refer to controls you have renamed. There are also a lot of seemingly extraneous code ....for example, the requery in your forms on-current seems redundant.

 

[sub_nitrox]

Hey, Thanks for your great support.
Now it works but only for the cboNOME_PROVIDER.
The NUM_MAM1 and also the other, let me only to select again the first value although I can see the selected righ value.
Have you any suggestion?

I have also another question about the code to put in the form to check if all the required field have been filled before to save them in the table.
CAn you help me on this issue?

Thanks for your patient. I'm not an expert programmer or better still i'm just a guy who want try to do something of good.

Thanks in advance.
Antonio

 

[rborob]

you need a checkform function i think. This will be called on a save button etc to make sure all necessary fields are filled in.

ok the function should look something like this

Private Function Checkform()

Dim strErrorMessage As String

     If IsNull(Me.whatever)=True  'can also use =0 ="" etc
          strErrorMessage = strErrorMessage & " - whatever" & vbCrLf
     End if
     'put more ifs for more boxes combos etc

CheckFormVerbal = strErrorMessage   
End Function

and in your event (save button?)

strTemp = Checkform()

If IsNull(strTemp) or strTemp = vbNullString or strTemp ="" Then
    'nothing
Else
    MsgBox("The following fields are not complete:" & vbCrLf & strTemp)
    Exit Sub
End if

hope that helps

 

[CraigDolphin]

Antonio,

in the combo box for NUM_MAM1 you have the bound column set to 3. If you look at the sql which is the row source for this box, you will see that the third column in the query is the NOME_PROVIDER field. When you select this the combo box is storing the value in the NOME_PROVIDER field but displaying the value in the second column of the sql query (you call tell by looking at the column widths property of your combo box the first column is 0 wide = invisible, the second is over an inch wide (visible), and the third column is also 0 wide). Since your row source has several records with the same NOME_PROVIDER and the combo box can only show one value, it defaults to displaying the first value in the query which meets the criteria.

To fix it, change the bound column to 2 instead of 3. This way, the combo box is storing the value in the second field of your sql query (NUM_MAM) which has only one value meeting the criteria.

 

[sub_nitrox]

Hi CraigDolphin,
thanks so much for your suggestion. Really perfect. It work well.

But, I have now a new issue related to the other combo box.
I'd like to implement an automatic selection. In details, when the user select the value in the NUM_MAM1, between all the NUM MAM displayed, I'd like to be able in the NUM_MAM2 to select the remaining value of NUM_MAM1 except the one already seòlected in the NUM_MAM1 field.

Is this possible?
Sorry for my incessant question, but this is something of useful for me that at the moment I'm not abel to implement.

Regards,
Antonio.

 

[sub_nitrox]

Hello to everybode,
anyone have suggestion for me on the topics posted before?

Thanks in advance.
Cheers,
Antonio.

 

[sub_nitrox]

Hi I'm waiting for some help on the issue posted before.

In the mainwhile, I want say thanks to robrob for the suggestion on how perform a check befpre to save all dates in the form.

I have n that only a doubt, and is about the part in red

Private Function Checkform()

Dim strErrorMessage As String

If IsNull(Me.whatever)=True 'can also use =0 ="" etc
strErrorMessage = strErrorMessage & " - whatever" & vbCrLf
End if
'put more ifs for more boxes combos etc

CheckFormVerbal = strErrorMessage
End Function

I got an error.
Is that what?
Sorry for this stupid question but I'm not so expert on programming.

Regards,
Antonio,