? simplifying code using quieries (1 Viewer)

[deweysanchez]

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

 

[ajetrumpet]

if it works, i would stick with it. that's what i do, until of course it gets out of hand and unmanagable, and too much "spaghetti" type...

 

[RuralGuy]

Are you aware of the fact that forms are simply windows into tables? You should really spend the time necessary to understand how to return that information with queries or you will be missing out on most of the power of a relational database. I am also curious why you need such varied data for your current form?

 

[deweysanchez]

I am entirely self taught on this and have no previous programming experience. Funnily enough, learning how to do this with quieries is exactly what I am trying to get at, but thanks.

 

[Niroth]

Question 1: Wouldn't it be better to create a relationship between this table and the table that contain those information, or create a subform that contains those information in this form, so you don't have to enter the information twice?

Or if you are not entering the information but simply want to display it, maybe you can create a query, and then use DLookup instead?

I suppose if you use an update query, you might save a few lines, but personally I think it's easier to just stick with what already works.