Hi - this is coming as a modification from one of my previous posts that were solved - on an activity screen the user who is logged in can only select companies that have the 'active' tickbox ticked and where the 'leadofficer' value must equal the value of the user logged in.
this is achieved by the following VBA
Private Sub Form_Load()
Me.companyname.RowSourceType = "Table/Query"
Me.companyname.RowSource = "SELECT [companyname] FROM [tblcompany] WHERE [active] = Yes AND [leadofficer] ='" & loginname & "'"
End Sub
where loginname is a global variable held in a module and stores the name of the user currenty logged in.
However i now need an additional leadofficer field - 'leadofficer2' which allows me to enter an additional leadofficer against a company. I know want to amend the above statement to allow a user who has his loginname in either 'leadofficer' or 'leadofficer2' to have the appropriate companies available in the drop down combo box PROVIDED that the active tick box on those companies is ticked - but i cant for the life of me modify the statement above to account for this.
Does anyone know the correct syntax i should be entering
Thanks again,
Rhys.
SELECT syntax in VBA for retrieving information
Vj5
Try this:
Me.companyname.RowSource = "SELECT [companyname] FROM [tblcompany] WHERE [active] = Yes AND ([leadofficer] ='" & loginname & "' or [leadofficer2]='" & loginame & "')"
Notes: you need to copy and paste the code so that single and double quote are entered.
Notes: you can stop at after above statement, and debug.print me.companyname.rowsource and copy the string in debug window and put in the sql workspace of a query to see if the sql is proper.
or
Me.companyname.RowSource = StrFmt("SELECT [companyname] FROM [tblcompany] WHERE [active] = Yes AND ([leadofficer] ='{0}' or [leadofficer2]='{0}')", loginname)
Function StrFmt(pFmtStr as string, ParamArray pV()) As string ' Idea comes from VB.NET String.Format(..) function
Dim mNParam As Integer, mL As Integer, mU As Integer
mL = LBound(pParam)
mU = UBound(pParam)
mNParam = mU - mL + 1
If mNParam = 0 Then
StrFormat = pS
Exit Function
End If
Dim mA As String, mP As Integer, J As Byte, I As Byte
For J = LBound(pParam) To UBound(pParam)
mA = "{" & I & "}": I = I + 1
mP = InStr(pS, mA)
While mP > 0
pS = Replace(pS, mA, pParam(J))
mP = InStr(pS, mA)
Wend
Next
StrFormat = pS
end Function
kenlefeb
Hi Rhys,
Your falling into a bit a trap if you go ahead with your approach. Say for example a third leadofficer is needed and then a fourth and so on. If you keep adding columns to the table for leadofficers your code will get messy and things get complicated. What you've got is a relation between companies and the leadofficers, I cannot say if its a one to many or a many to many relationship you'll need to decide that yourself.
Either way your wanting additional tables that lets you specify leadofficers to companies. for example
Instead of a table like this
Company, LeadOfficer1, LeadOfficer2
1, jim, bob
You want a new table that will be something like this,
Company, LeadOfficer
1, jim
1, bob
You can then create a query that joins your copany/leadofficer tables together and instead of running the SELECT statement on the table you run it against the query.
The solution to this lies in changing the structure of your database not the code.
sureshv
Hi there - thanks for that but i cant get the SQL syntax to work - thought id try the first example you gave because it would be useful although i have resolved the issue based on Derek's suggestion - when you open the form it doesnt give a run time error but it doesnt apply the statement to the second lead officer, only the first.
Rhys.
Chris Honcoop
Thanks again Derek!
Rhys.