How to read values from a table?

[Shri]

Hi,

I am trying to redesign an old database. I have three forms start, entry,
edit forms. Start form is like a switch board to enter into the forms. An
audit is entered into the database through "Entry" form which has around 50
controls. All these controls gets stored in the main table. "Edit" forms
allows you to edit the audit. The issue here is the "Edit" form doesn't read
the values from the main table when the form is loaded. I have a logic to
read the values from the main table in "start" form. Everything looks fine
with the SQL statement but it doesn't work. I also try to step inot the code
but no luck. Can anyone please help me with this.

Thanks,

 

[Arvin Meyer [MVP]]

Karnac says:

Show us the code and the SQL statement.

 

[Shri]

Below is the code in "Start" form.

******************************************
Private Sub Option5_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
Dim dbslog As Database, rstlog As Recordset, sSql As String, AuditType As
String
Dim Social As String, AppSeq As String, Audit As String

If Option6 = -1 Then
stDocName = "frm_edit_processor"
End If

Option5 = 0
Social = SSN.value
AppSeq = App.value
If Option1 = -1 Then
Audit = "Proofer"
ElseIf Option2 = -1 Then
Audit = "Analyst"
ElseIf Option3 = -1 Then
Audit = "Appraiser"
ElseIf Option6 = -1 Then
Audit = "Processor"
End If

If option_edit = -1 And (Option1 = -1 Or Option2 = -1 Or Option3 = -1 Or
Option6 = -1) Then
If IsNull(SSN.value) Or IsNull(App.value) Then
MsgBox "Please enter a Social and App Sequence Number", , version
SSN.SetFocus
Exit Sub
End If

stLinkCriteria = "[SSN] = '" & Social & "' and [AppSeq]= '" & AppSeq & "'
and [Audit_Type] = '" & Audit & "'"

If Option1 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Proofer'"
AuditType = "a Proofer Audit"
ElseIf Option2 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Analyst'"
AuditType = "an Analyst Audit"
ElseIf Option3 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Appraiser'"
AuditType = "an Appraiser Audit"
ElseIf Option6 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Processor'"
AuditType = "a Processor Audit"
End If

Set dbslog = CurrentDb
Set rstlog = dbslog.OpenRecordset(sSql, dbOpenSnapshot)

If rstlog.RecordCount < 1 Then
MsgBox "Couldn't find SSN " & Social & " and App Sequence# " & AppSeq &
" as " & AuditType, , version
Exit Sub
End If

rstlog.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub

*************************************************

Option5 is "Go" button. Option6 is "Edit". The above code used to work fine
earlier. But I am trying to redesign it and doesn't work now. I am not sure
if this helps to understand the issue.

Thanks in Advance.

 

[Shri]

I am able to solve the issue.

Thanks.

Below is the code in "Start" form.

******************************************
Private Sub Option5_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
Dim dbslog As Database, rstlog As Recordset, sSql As String, AuditType As
String
Dim Social As String, AppSeq As String, Audit As String

If Option6 = -1 Then
stDocName = "frm_edit_processor"
End If

Option5 = 0
Social = SSN.value
AppSeq = App.value
If Option1 = -1 Then
Audit = "Proofer"
ElseIf Option2 = -1 Then
Audit = "Analyst"
ElseIf Option3 = -1 Then
Audit = "Appraiser"
ElseIf Option6 = -1 Then
Audit = "Processor"
End If

If option_edit = -1 And (Option1 = -1 Or Option2 = -1 Or Option3 = -1 Or
Option6 = -1) Then
If IsNull(SSN.value) Or IsNull(App.value) Then
MsgBox "Please enter a Social and App Sequence Number", , version
SSN.SetFocus
Exit Sub
End If

stLinkCriteria = "[SSN] = '" & Social & "' and [AppSeq]= '" & AppSeq & "'
and [Audit_Type] = '" & Audit & "'"

If Option1 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Proofer'"
AuditType = "a Proofer Audit"
ElseIf Option2 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Analyst'"
AuditType = "an Analyst Audit"
ElseIf Option3 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Appraiser'"
AuditType = "an Appraiser Audit"
ElseIf Option6 = -1 Then
sSql = "Select * FROM tbl_appraisalreview WHERE SSN = '" & Social & "'
and AppSeq = '" & AppSeq & "' and audit_type = 'Processor'"
AuditType = "a Processor Audit"
End If

Set dbslog = CurrentDb
Set rstlog = dbslog.OpenRecordset(sSql, dbOpenSnapshot)

If rstlog.RecordCount < 1 Then
MsgBox "Couldn't find SSN " & Social & " and App Sequence# " & AppSeq &
" as " & AuditType, , version
Exit Sub
End If

rstlog.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub

*************************************************

Option5 is "Go" button. Option6 is "Edit". The above code used to work fine
earlier. But I am trying to redesign it and doesn't work now. I am not sure
if this helps to understand the issue.

Thanks in Advance.

Karnac says:

Show us the code and the SQL statement.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com