Last active
January 15, 2019 09:11
-
-
Save renatomoor/359574346ad7f128d71e048b3872ab42 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'Creating Function to do a query and then fill the list with the results | |
Private Function doQueryAndFill(query As String, listName As String) | |
Dim results As recordSet | |
'test if the id is not null // Replace id_Person | |
If Not IsNull(Me.id_Person.Value) Then | |
Set results = CurrentDb.OpenRecordset(query) | |
Me(listName).RowSourceType = "Value List" | |
Me(listName).RowSource = "" | |
'fill the list with the results | |
Do While Not results.EOF | |
Me(listName).AddItem results.Fields(1) | |
results.MoveNext | |
Loop | |
'Close DB and Clean variable results | |
CurrentDb.Close | |
Set results = Nothing | |
End If | |
End Function | |
Private Sub Form_Current() | |
'Hide the navigation buttons | |
Me.RecordSelectors = False | |
Me.NavigationButtons = False | |
'When adding a new person, clear the lists | |
If Form.NewRecord = True Then | |
'//Replace all the list for the name of your lists | |
Me("ListEmails").RowSource = "" | |
Me("ListPhones").RowSource = "" | |
Me("ListNationalities").RowSource = "" | |
Me("ListAddresses").RowSource = "" | |
End If | |
'Declare all the query variable, you can name this as you want | |
Dim queryEmail As String | |
Dim queryPhone As String | |
Dim queryNationality As String | |
Dim queryAdress As String | |
Dim queryEnterprise As String | |
'As the where condition is always the same, is better to do a variable | |
Dim WhereCondition As String | |
'This is the last line of your query // you need to adapt the hole line with your variables | |
WhereCondition = "WHERE T_Person.id_Person=" & Me.id_Person.Value & "" | |
'Now you need do your querys, do not forget the space ↓ at the end of each line | |
queryEmail = "SELECT T_Person.id_Person, T_Email.adresse_Email " & _ | |
"FROM T_Person INNER JOIN (T_Email INNER JOIN T_Person_Email ON T_Email.id_Email = T_Person_Email.FK_Email) " & _ | |
"ON T_Person.id_Person = T_Person_Email.FK_Person " & _ | |
WhereCondition 'the last line is the variable WhereCondition. | |
queryPhone = "SELECT T_Person.id_Person, T_Phone.number_Phone " & _ | |
"FROM T_Phone INNER JOIN (T_Person INNER JOIN T_Person_Phone ON T_Person.id_Person = T_Person_Phone.FK_Person) " & _ | |
"ON T_Phone.id_Phone = T_Person_Phone.FK_Phone " & _ | |
WhereCondition | |
queryNationality = "SELECT T_Person.id_Person, T_Nationality.nationality_Nationality " & _ | |
"FROM T_Person INNER JOIN (T_Nationality INNER JOIN T_Person_Nationality ON T_Nationality.id_Nationality = T_Person_Nationality.FK_Nationality) " & _ | |
"ON T_Person.id_Person = T_Person_Nationality.FK_Person " & _ | |
WhereCondition | |
queryAdress = "SELECT T_Person.id_Person, T_Address.Name_Address " & _ | |
"FROM T_Person INNER JOIN (T_Address INNER JOIN T_Person_Address ON T_Address.id_Adresse = T_Person_Address.FK_Address) " & _ | |
"ON T_Person.id_Person = T_Person_Address.FK_Person " & _ | |
WhereCondition | |
queryEnterprise = "SELECT T_Person.id_Person, T_Enterprise.name_Enterprise " & _ | |
"FROM T_Person INNER JOIN (T_Enterprise INNER JOIN T_Enterprise_Person ON T_Enterprise.id_Enterprise = T_Enterprise_Person.FK_Entrepirse) " & _ | |
"ON T_Person.id_Person = T_Enterprise_Person.FK_Person " & _ | |
WhereCondition | |
'Call the function doQueryAndFill with the query as the first parameter and the the name of the list as the second | |
Call doQueryAndFill(queryEmail, "ListEmails") '//Replace the parameters with your variables and lists names. | |
Call doQueryAndFill(queryPhone, "ListPhones") | |
Call doQueryAndFill(queryNationality, "ListNationalities") | |
Call doQueryAndFill(queryAdress, "ListAddresses") | |
Call doQueryAndFill(queryEnterprise, "ListEnterprises") | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment