Macro to open table to a specific record

[Stretholox]

Hello.



I'm very new to access database and I'm trying to create a macro that allows the user to enter data after seeing a mistake in a form. I need a macro that will open the specific table and record of a piece of data. For example there is a student TestName3 whose grade on a competency is 30% but should really be 45%. I want a macro by the competency percentage to allow the teacher to edit that without looking through the entire table. The macro should prompt first to ask if the teacher is sure they would like to edit, second prompt asking for the student's unique ID number, and finally be taken to the specific record and table related to that competency.

In my infinite stupidity I have tried to use a vastly overcomplicated DLookup and Order column to give me the value for a acGoTo search.

Here's what I have.

Option Compare Database
'------------------------------------------------------------
' Command71_Click
'
'------------------------------------------------------------
Dim answer As String
Dim response As Object
Dim gotoresponse As Object

Private Sub Command71_Click()
On Error GoTo Command71_Click_Err

If MsgBox("Are you sure you want to edit this Competency?", vbYesNo, "Selection") = vbNo Then Exit Sub
response = InputBox("What is the student's UFID", "Student UFID", acFirst)
DLookup("[Order]", "Competency", "[UFID]=" & response) = response
gotoresponse = CInt(response)
DoCmd.OpenTable "Competency", acViewNormal, acEdit
DoCmd.GoToRecord acDataTable, "Competency", acGoTo, gotoresponse

Command71_Click_Exit:
Exit Sub
Command71_Click_Err:
MsgBox Error$
Resume Command71_Click_Exit
End Sub

Please help!!!

[rpeare]

is your form bound or unbound?
it looks like it's bound, if so you can add a filter to your form or, my preference, is to have the form based on a query (or modify the record source of the form) so that only that record is available for editing.

[Stretholox]

Yea the problem is that the form holds data from linked tables so it's read only. That's why I am trying to go through all this mess. I don't want the users to have to weed through the tables EVER. I want a macro that will allow them to go directly where they want to be. But everything I've tried is failing. So I created a seperate form that is just the competency information. And I'm trying this new code.
Dim rs As Object
Dim gtr As String

Private Sub Command71_Click()
On Error GoTo Command71_Click_Err

If MsgBox("Are you sure you want to edit this competency?", vbYesNo) = vbNo Then Exit Sub
gtr = Forms!AxiumCompilation.Controls!UFID
DoCmd.OpenForm "Competency"
Set rs = Forms![Competency].RecordsetClone
rs.FindFirst "UFID =" & gtr
If rs.NoMatch Then
DoCmd.GoToRecord acForm, "Competency", acFirst
Set rs = Nothing



End If

Command71_Click_Exit:
Exit Sub
Command71_Click_Err:
MsgBox Error$
GoTo Command71_Click_Exit

End Sub


And it is just ignoring the gtr = Forms!AxiumCompilation.Controls!UFID part. Do you know how I should be identifying that. I've also had problmes with Dim gtr As String, Variant, Long or Object.

[rpeare]

You are over complicating this I think.

Base your form on a query. In that query in your criteria prompt the user for the UFID

[Enter the Student ID Number]

If you have a single record for each student you'll get one value
If you have multiple records for each student you'll get all of them

You don't need the code you're using when you can limit the data they see by setting up the form to run from a query.

[Stretholox]

You are over complicating this I think.

Base your form on a query. In that query in your criteria prompt the user for the UFID

[Enter the Student ID Number]

If you have a single record for each student you'll get one value
If you have multiple records for each student you'll get all of them

You don't need the code you're using when you can limit the data they see by setting up the form to run from a query.

I was able to do that with a prompt and without a query. I was hoping there was a way to do it without any prompting.

Additionally, I don't want the navigation panel to be cluttered with many perhaps outdated or unwanted queries. I just wanted a simple way to correct and edit data with as little risk of mixups as possible. Unfortunately, the tables I'm using for the competency data double as CSVs for a grading site upload. There are some frustrating limitations that I'm trying to overcome with as much automation as possible.

[rpeare]

then set a criteria for the query to be the value in the code you're currently using:

Forms!AxiumCompilation.Controls!UFID

instead of

[Enter the Student ID] or whatever