Finding a record and assigning it to a variable

[kohler0529]

Hi everyone,

I am new to the forum so I apologize in advance if I am posting a redundant thread. I have looked through some of the VBA threads and haven't found anything exactly on point. I am writing code that begins a dialog of input boxes. I need to enter a number into one of the input boxes, and have access take that variable and search through a table to find a specific record. Then I need access to take certain fields in that record and assign them to variables for input into another table. I have been unable to correctly code the lookup process. Here is the code I have tried so far:

Private Sub btnSG_Click()

Dim strMCLcrm As Long
Dim SGmCL As String
Dim SGgroup As String
Dim SGclass As String
Dim SGmax As String
Dim PRVperson As String
Dim OVperson As String
Dim PRVpubOrder As String
Dim OVpubOrder As String
Dim PRVpubSfty As String
Dim OVpubSfty As String
Dim PRVconSub As String
Dim OVconSub As String
Dim myMessage As String
Dim MCLMessage As String
Dim CnDte As String
Dim frmPresentCharge As DAO.Field
Dim dbs As DAO.Database
Dim MCLrst As DAO.Recordset
Dim MCLtdf As TableDef
Dim idxMCL As Index
Dim default As String
Dim CrntDte As Date
Dim ConAge As Long
Dim TenYrCon As String






myMessage = InputBox("Does the client have a prior record? Please indicate either yes, or no.")
'if yes then, what was the date of the conviction? If no, end inquiry.
If myMessage = "yes" Then
CrntDte = Date
CnDte = InputBox("What was the date of the conviction?")
ConAge = DateDiff("yyyy", CnDte, Date)
'myMessage = Nothing

If ConAge < 10 Then

MCLMessage = InputBox("Please input the MCL number, section, and any subsections client was previously charged with.")

strMCLcrm = MCLMessage

'search MCLMessage in tblMCLcrmlw and assign tblMCLgrp, tblMCLcls and tblMCLStatMx to variables_

THIS IS WHERE I AM HAVING TROUBLE, RIGHT NOW I AM GETTING A DATA INCOMPATIBILITY ERROR, THE CODE COMMENTS ALSO INCLUDE OTHER METHODS I HAVE TRIED;

Set dbs = CurrentDb
Set MCLrst = dbs.OpenRecordset("tblMCLcrmlw", dbOpenDynaset)
'Set MCLtdf = dbs.TableDefs!tblMCLcrmlw
With MCLrst
.FindFirst "tblMCLnbr=" & strMCLcrm
.Edit
SGgroup = !tblMCLgrp
End With

MsgBox (tblMCLgrp)
End If

'strMCLcrm = DLookup("tblMCLcrmlw","tblMCLnbrSub","RecordID=5")

'place those variables in a new table of the client's priors which will be used in calculations.


End If
'if conviction > 10 years old, end inquiry, if not, then,
'what was the defendant charged with?



myMessage = InputBox("Are there any other convictions?")
'if yes, loop the above process, if no end inquiry






'Does the client have a prior record? Put a caption in the message box,_
'STEP I. Score the Prior Record Variables
'what is the date of the prior conviction, ten year gap rule? if only_
'than ten years, move on to the next charge or terminate dialogue
'if yes, open form and select offense, select by number, not by title
'assign variables to felonies by MCL #'s in table, group, class and stat max,
'run dialogue through all offense tables and assign values to variables;





End Sub

Any help is much appreciated. Thanks in advance.

 

[kohler0529]

Thank you so much for your reply. You have definitely pointed me in the right direction with the use of a query. I actually did not know that could be done with code; shows you that I know just enough to be dangerous. To answer some of your questions, I am not editing the recordset, I thought I had to use the edit command in order to search for a record. My user is me, I am not developing this for commercial use, and the data I am looking up are sentencing guidelines in criminal cases. They rarely change, and when they do, I can easily update them. Once again, thank you very much. I will try the query as you suggest.