cbo field with ID from 1ste cbo

[132433]

I have a cbo that must display "all" the clubs from the tabel [tblClubs] that are availeble in the selectect season in the first cbo.

The work flow is;
choose a season (this is in [cboSeizoen_inschrijven]) That have the value of all my seasons.
Than in the next cbo [cboClubs_Inschrijven] we must choose the club to subscibe. when that is done push the subscribe button en a new form must open with the selectect club specs.

So I need a good in my 2nd cbo that says.

display all the record from the field Clubs in the [tblClubs] that have the [SeizoenII] that is selected in the 1ste cbo [cboSeizoen_inschrijven]

I hope some one can help me with the first step of mu sunscribe procudé

 

[Cronk]

SELECT Clubs FROM tblClubs WHERE [SeizoenII]=Forms!YourForm![cboSeizoen_inschrijven] ORDER BY Clubs

 

[132433]

Hello Cronk,

I'am lost..

Sorry but someting is wrong with my head, I have made some mistakes. Damn....

SELECT ClubCode FROM tblClubs WHERE [SeizoenID]=Forms!frmLeerling![cboSeizoen_inschrijven] ORDER BY ClubCode

I have made some changes because I give you the wrong names...

This is how I read it !! Is this OK??

[Clubcode] is the value I wan't to see, and it's in the tbl [tblClubs] with the ID off the season [SeizoenID] that is sellected in the frm [frmLeerling] in the cbo [cboSeizoen_Inschrijven] order by ClubCode

Than I understand the code but when I put the whole line in the Element he says a syntaxis hase to be checkt and I must place () for the subquery ??

HELPPPPPPPPPP

 

[Pat Hartman]

The query needs to go into the RowSource property of the second combo. Then in the AfterUpdate event of the first combo, you need a single line of code.

Me.cboSeizoen_inschrijven.Requery

 

[132433]

When I select now a season in the first combo he says

can't find object ME.

In the 2nd combo the first season displays the right clubs the second season is the same as the first. That's not OK??

 

[Mihail]

Try to move Pat's code under the _Click event of the first combo.

 

[132433]

same result?????

can't find object ME.

In the 2nd combo the first season displays the right clubs the second season is the same as the first. That's not OK??

 

[Cronk]

In design view, right click on the combo to bring up the Properties window. Click on the Events tab. In the AfterUpdate event, click on the ellipses (dots) on the right hand side, and select Code Builder. In the code window, put the code under the line
Sub cboSeizoen_inschrijven_AfterUpdate()

 

[Mihail]

Post the code you use.

 

[132433]

Here is a Printsreen,

When I put in the Code now it's inmediatly RED???

http://www.allezkidz.nl/cbo_Printscreen_And_Code.jpg


The Code

Option Compare Database
Const strVelden = "LLID; LLNummer; Voornaam; Achternaam; Geboortedatum; Woonplaats; Email"
Private Sub cboClubs_Inschrijven_AfterUpdate()
SELECT ClubCode FROM tblClubs WHERE [SeizoenID]=Forms!frmLeerling![cboSeizoen_inschrijven] ORDER BY ClubCode;
End Sub
Private Sub cboEmailDomein_NotInList(NewData As String, Response As Integer)
If MsgBox("De opgegeven domeinnaam is onbekend!" & vbLf & vbLf & "Toevoegen?", vbYesNo, "Onbekend") = vbYes Then
    Response = acDataErrAdded
    DoCmd.RunSQL "INSERT INTO tblEmailDomein(Domeinnaam) VALUES('" & NewData & "')"
Else
    Response = acDataErrContinue
    Me.cboEmailDomein.Undo
End If
End Sub
Private Sub cboFindLL_Records_Input_Enter()
    Me.cboFindLL_Records_Input.Dropdown
End Sub
Private Sub cboFindLL_Records_Input_Click()
Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LLID] = " & Str(Nz(Me![cboFindLL_Records_Input], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub cboP_EmailDomein_AfterUpdate()
Dim AT As Integer
 
 If Right(Me.P_Email, 1) = "@" Then
  Me.P_Email = Me.P_Email & Me.cboP_EmailDomein
 Else
   AT = InStr(Me.P_Email, "@")
    If AT > 0 Then
     Me.P_Email = Left(Me.P_Email, AT) & Me.cboP_EmailDomein
    End If
  End If
 
Me.cboP_EmailDomein = Null
End Sub
Private Sub cboP_EmailDomein_GotFocus()
 Me.cboP_EmailDomein.Dropdown
End Sub
Private Sub cboP_EmailDomein_NotInList(NewData As String, Response As Integer)
If MsgBox("De opgegeven domeinnaam is onbekend!" & vbLf & vbLf & "Toevoegen?", vbYesNo, "Onbekend") = vbYes Then
    Response = acDataErrAdded
    DoCmd.RunSQL "INSERT INTO tblEmailDomein(Domeinnaam) VALUES('" & NewData & "')"
Else
    Response = acDataErrContinue
    Me.cboP_EmailDomein.Undo
End If
End Sub
Private Sub cboSeizoen_Inschrijven_AfterUpdate()
End Sub
Private Sub Email_BeforeUpdate(Cancel As Integer)
 
 If InStr(Me.Email, "@") = 0 Then
  MsgBox "Voer een geldig emailadres in met @!"
  Cancel = True
 End If
End Sub
Private Sub Email_Change()
 
 If Right(Me.Email.Text, 1) = "@" Then
  cboEmailDomein.SetFocus
 End If
End Sub
Private Sub cboEmailDomein_GotFocus()
 Me.cboEmailDomein.Dropdown
End Sub
Private Sub cboEmailDomein_AfterUpdate()
 
 Dim AT As Integer
 
 If Right(Me.Email, 1) = "@" Then
  Me.Email = Me.Email & Me.cboEmailDomein
 Else
   AT = InStr(Me.Email, "@")
    If AT > 0 Then
     Me.Email = Left(Me.Email, AT) & Me.cboEmailDomein
    End If
  End If
 
Me.cboEmailDomein = Null
End Sub
Private Sub Form_Current()
    With Me.cboFindLL_Input
        .RowSourceType = "Value List"
        .RowSource = strVelden
    End With
End Sub
Private Sub cboFindLL_Input_Click()
Dim arrVelden
Dim strSQL As String, RestVelden As String
Dim i As Integer
    arrVelden = Split(strVelden, ";")
    strSQL = "SELECT " & arrVelden(LBound(arrVelden))
    For i = LBound(arrVelden) + 1 To UBound(arrVelden)
        If Trim(arrVelden(i)) = Me.cboFindLL_Input Then
            strSQL = strSQL & ", " & Trim(arrVelden(i))
        Else
            RestVelden = RestVelden & ", " & Trim(arrVelden(i))
        End If
    Next i
    strSQL = strSQL & RestVelden & " FROM tblLeerLing ORDER BY " & Me.cboFindLL_Input
    Me.cboFindLL_Records_Input.RowSource = strSQL
    Me.cboFindLL_Records_Input.Requery
End Sub
Private Sub Nummer_AfterUpdate()
Dim Adres As DAO.Recordset
Dim sqlZ As String
Dim srt1 As Byte
Dim srt2 As Byte
If Me![Nummer] <> "" Then
  If Me![Nummer] Mod 2 = 0 Then
    srt1 = 1
    srt2 = 1
  Else
    srt1 = 0
    srt2 = 0
  End If
Else
  Me![Nummer] = 0
  srt1 = 2
  srt2 = 3
End If
sqlZ = "SELECT Straat, Plaats, Gemeente, Provincie FROM (Plaats INNER JOIN Straat ON Plaats.PlaatsID = Straat.PlaatsID) " & _
       "INNER JOIN Postcodes ON Straat.StraatID = Postcodes.StraatID " & _
       "WHERE (Postcode = '" & Me![Postcode] & "' AND " & _
               "Van <=" & Me![Nummer] & " and " & _
               "Tem >=" & Me![Nummer] & " and " & _
               "(Soort = " & srt1 & " OR Soort = " & srt2 & "))"
If Me![Nummer] = 0 Then
  Me![Nummer] = ""
End If
Set Adres = CurrentDb.OpenRecordset(sqlZ)
If Adres.BOF Then
    MsgBox "Postcode/nummer niet gevonden", vbExclamation
Else
    Me.Straat = Adres!Straat
    Me.Plaats = Adres!Plaats
    Me.Adres = Me.Straat & " " & Me.Nummer & vbLf & Me.Postcode & " " & Adres!Plaats
End If
Adres.Close
End Sub
Private Sub LLFoto_Delete_Click()
End Sub
Private Sub LLFoto_New_Click()
End Sub
Public Function NewFoto()
End Function
Private Sub P_Email_BeforeUpdate(Cancel As Integer)
 If InStr(Me.P_Email, "@") = 0 Then
   MsgBox "Voer een geldig emailadres in met @!"
  Cancel = True
 End If
End Sub
Private Sub P_Email_Change()
 If Right(Me.P_Email.Text, 1) = "@" Then
  cboP_EmailDomein.SetFocus
 End If
End Sub
Private Sub Paar_AfterUpdate()
End Sub
Private Sub Postcode_AfterUpdate()
If DCount("*", "Postcodes", "Postcode = '" & Me.Postcode & "'") = 0 Then
  MsgBox "Postcode bestaat niet", vbExclamation
End If
End Sub

 

[Mihail]

If it is red, then is BAD. Very bad. It don't work

Pat said:

Then in the AfterUpdate event of the first combo, you need a single line of code.
Me.cboSeizoen_inschrijven.Requer

I said:

Try to move Pat's code under the _Click event of the first combo.

So, we refere to the same code:

Me.cboSeizoen_inschrijven.Requery

THIS code should be in the after event or (in my opinion) in the on click event. Nothing else. I advice you to follow Pat's approach. She is a lot more skilled than me.

Cronk give you the code and detailed instructions about how to use it.
Maybe he will be so kind to explain again because my English is not enough for this task.

@Pat and Cronk
If you will paste 132433's code in the VBA editor you will see immediately where error is (red code)

 

[132433]

The first combo is OK now... I can switch between them.

But the second combo is empty and when I'am in the form and I go to the next combo where the clubs must apere the VBA displays the red line and the word ClubCode is Blue... When I rightclick on it and go to the proporties. I can't see ClubCode in the list???


@Mihail

THIS code should be in the after event or (in my opinion) in the on click event. Nothing else. I advice you to follow Pat's approach. She is a lot more skilled than me.

Both options are working!

 

[MSAccessRookie]

The SQL Code below (taken from the posted VBA Code) should not be in the in the VBA Module, and instead needs to be in the Row Source Property of the cboClubs_Inschrijven Combo Box.

Open up the Form in Design Mode, select the Combo Box Property Sheet, and update the Row Source Property to contain the SQL Code.

-- Rookie

SELECT ClubCode FROM tblClubs WHERE [SeizoenID]=Forms!frmLeerling![cboSeizoen_inschrijven] ORDER BY ClubCode;

 

[132433]

Them, I think the 30gr. celcius here is boiling my brains.

I have read the hole thing again, and i found out that I must put the code

SELECT clubCode FROM tblClubs WHERE [SeizoenID]=Forms!frmLeerling![cboSeizoen_Inschrijven] ORDER BY ClubCode;

in the rowsource of the second combo.
So away of the VBA and straight into the rowsource properties.

And yes i'am a step closer to home.
The first combo is looking good and the second for 50%. when I switch between the seasons in combo 1 the list of value in the second combo apaers, BUT the are both the same. ?????

@msaccesrookie
We are typing @ the same time Thanks

 

[Cronk]

I don't know how more direct we can be.

In post #4, there was some code that you were directed to put in the AfterUpdate event for combo1.

You instead put the SQL for the rowsource of combo 2 in the AfterUpdate event.

Read post #4 again, slowly.

 

[Pat Hartman]

If you reread my original reply, I did tell you to put the query in the control's RowSource property. You put it in the AfterUpdate event. I'm not sure what you did with the .Requery. The .Requery needs to go into the AfterUpdate event of the control

Private Sub cboClubs_Inschrijven_AfterUpdate()
    Me.cboClubs_Inschrijven.Requery
End Sub

The Click event may work for you since this is a combo but the AfterUpdate event fires before the click event and that is the event I always use.

 

[132433]

The query needs to go into the RowSource property of the second combo. Then in the AfterUpdate event of the first combo, you need a single line of code.

Me.cboSeizoen_inschrijven.Requery

Hello Cronk,

Just like I type above.. I all ready discovered this mistake.. because I was reading the hole thing twice again.

And it seams to be working almost .. the fist combo is perfect the second combo is the following wrong

To test the thing ones againg I made a new season in [tblSeizoen] and put some new Clubs with [tblClubs] in the season. Know the second combo only shows this two clubs.
It seems that the combo reads the action of the last modified Season..
My feeling say's , Do I need a code to close the clear the memory of the latest action for seasons??

@PAt, Thanks also.... I'am realy :banghead: sorry.. CAn you take a look @ this last problem..

 

[132433]

I have found the problem, But how to cancel it.

It's not like i sayd above. Because when i close my frm [frmLeerling] and open The first choice i make in the first combo is OK. SUPER.
But when I want to resider my choise and choose a knew seoson, the second combo is'nt getting teh new information.

 

[Mihail]

Upload the database

 

[132433]

It is the frm [frmLeerling]

http://www.allezkidz.nl/DPadmin_test.rar