Simplifying VBA code by using Queries

deweysanchez · August 14th, 2009, 09:54 AM

Hi,

I have been trying to use queries to retrieve data from other forms and have failed miserably. I have therefore cobbled together the following code to do the job instead (see below). My question is: Would it simply the code if I were to use quieries instead? or is it worth changing it to using quieries and if so could someone please help with the coding?

Hugs n' Stuff
Dewey

Private Sub Form_Open(Cancel As Integer)

Dim stDeptDoc As String
Dim stStaffDoc As String
Dim stJobDoc As String

Dim stDeptlink As String
Dim stStaffLink As String
Dim stJobLink As String

Dim DeptN1 As String
Dim JobN1 As String
Dim DeptN2 As String
Dim JobN3 As String
Dim UserN As String

Me!UserName = CurrentUser

stStaffDoc = "STAFFLISTForm"
stStaffLink = "[StaffMember]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stStaffDoc, , , stStaffLink, acFormReadOnly, acHidden
DeptN1 = Forms!STAFFLISTForm!Department
JobN1 = Forms!STAFFLISTForm!JobTitle
UserN = Forms!STAFFLISTForm!ID
DoCmd.Close acForm, "STAFFLISTForm", acSaveNo

On Error GoTo DeptError
stDeptDoc = "DEPARTMENTForm"
stDeptlink = "[ID]=" & DeptN1
DoCmd.OpenForm stDeptDoc, acNormal, , stDeptlink, acFormReadOnly, acHidden
DeptN2 = Forms!DEPARTMENTForm!Department
DoCmd.Close acForm, "DEPARTMENTForm", acSaveNo

On Error GoTo JobError
stJobDoc = "JOBTITLEForm"
stJobLink = "[ID]=" & JobN1
DoCmd.OpenForm stJobDoc, , , stJobLink, acFormReadOnly, acHidden
JobN2 = Forms!JOBTITLEForm!JobTitle
DoCmd.Close acForm, "JOBTITLEForm", acSaveNo

Me!JobName = JobN2
Me!DeptName = DeptN2

Exit Sub
DeptError:
If Err.Number = 2427 Then
DeptN2 = "No Department"
Resume Next
End If

JobError:
If Err.Number = 2427 Then
JobN2 = "No Job Title"
Resume Next
End If

End Sub

allenm · August 31st, 2009, 06:27 PM

Just open your own instance to a joined recordset and pull the values for both directly instead of having to touch each form. Let the engine do the work for you.

The 1 thing you need to remember is that in join tables if there is a field named the same and you take * then the like named fields have a field name in your query of tablename.fieldname instead of just fieldname.

When you left join a table all records of the left most table show and if no record in the joining table exists then the field still exists as nothing. I commonly concatenate "" to get around the types of nothing issue and check it as a string null especially when unsure of what type is being sent. It's quick and dirty but works well.

So your code would look something like this:

Code:

Dim rsSource As Recordset, sJob As String, sDept As String
Set rsSource = CurrentDB.OpenRecordSet("Select * From (StaffTable Left Join DepartmentTable On StaffTable.Department = DepartmentTable.ID) Left Join TitleTable On StaffTable.JobTitle = TitleTable.ID")
'A good idea to always check for no return records
If rsSource.RecordCount < 1 Then
  'Error to show / What to do if no records return
Else
  sJob = rsSource("TitleTable.JobTitle").Value & ""
  If sJob = "" Then sJob = "No Job Title"
  sDept = rsSource("Department").Value & ""
  If sDept = "" Then sDept = "No Department"
End If
rsSource.Close

Hope this helps point you in the right direction.