I want to create a query off of the current record being viewed in a form. Is this possible? the objective would be to take the current information and have it eventually sent in an email. I cannot seem to figure out if there is a way to query off of a the current viewed record in a form? I feel as though there is something terribly obvious i am missing. Can anyone help?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Creating Query off of Current Record in Form
- Thread starter dhersch
- Start date
The only thing I can think of is having a checkbox that you want to edit the record and then basing the query off of this. I do not want to use this method. I have combed through similar topics for hours and am desperate for another method. Is there anything out there?
Upvote
0
I thought about something like that once upon a time.
Try this.
Create a "Continuous Form" - this allows you to layout your fields without showing it a view that resembles a table/spreadsheet. Add a button to the right. This will put that button on each record. Clicking that button will by default 'select' that same record for other operations.
In the click event, do something like:
The above IF is just a test that the two fields, task_name & task_seq have string values in them. My form is 'based' on a table. Click event then passes the field values to a function.
Beginning of that function looks like:
This uses the passed values to build a SQL query which is used to create a recordset. I then use the recordset to read the other fields and do whatever I need to do. In this particular case, I'm scripting a series of actions to perform. I have a button that executes all the actions and a button that allows me to run a single action (like above)
I will emphasize, this is just one way to do it. May not even the best way but it works very well for me.
Mike
Try this.
Create a "Continuous Form" - this allows you to layout your fields without showing it a view that resembles a table/spreadsheet. Add a button to the right. This will put that button on each record. Clicking that button will by default 'select' that same record for other operations.
In the click event, do something like:
Code:
Private Sub btnCommit_Click()
If Len(Me.task_name) > 0 And Len(Me.task_seq) > 0 Then
Call BeginTransaction(Me.task_name, Me.task_seq)
End If
DoCmd.Echo True
End SubThe above IF is just a test that the two fields, task_name & task_seq have string values in them. My form is 'based' on a table. Click event then passes the field values to a function.
Beginning of that function looks like:
Code:
Sub BeginTransaction(ByRef strGroup As String, Optional ByRef strScope As String)
'strGroup is the task_name, strScope is used when it's just one process to run
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strTask, curFolder As String
Dim strVal(), strFormat, strSingle As String
Dim x, y, cntRec As Integer
Set dbs = CurrentDb()
DoCmd.Echo True
strTask = FindDefaults("DefaultTaskList") ' Name of Table with Tasklist
strSQL = "SELECT * FROM " & strTask
strSQL = strSQL & " WHERE task_name='" & strGroup & "'"
If Len(strScope) > 0 Then
strSQL = strSQL & " AND task_seq=" & strScope
End If
strSQL = strSQL & " ORDER BY task_seq"
Set rs = dbs.OpenRecordset(strSQL)This uses the passed values to build a SQL query which is used to create a recordset. I then use the recordset to read the other fields and do whatever I need to do. In this particular case, I'm scripting a series of actions to perform. I have a button that executes all the actions and a button that allows me to run a single action (like above)
I will emphasize, this is just one way to do it. May not even the best way but it works very well for me.
Mike
Upvote
0
What bat17 wrote is correct. You need a unique key to identify the records. That's actually what I was doing, in a sense. I wanted to write a single Function that did: 1) Allowed me to execute a single task (current selected record) or 2) Execute a series of tasks.
Best way to manage the latter, for me, was to identify all tasks with the same name, and then assign each a sequence number. I could easily have included both values within a single field, just didn't feel the need.
aka, hence the comment just below the beginning of my Sub.
If you don't have a unique key, this task gets alot harder. Can be done, but you'd have to write functions to navigate through the records so it could know which is the selected record.
Mike
Best way to manage the latter, for me, was to identify all tasks with the same name, and then assign each a sequence number. I could easily have included both values within a single field, just didn't feel the need.
aka, hence the comment just below the beginning of my Sub.
If you don't have a unique key, this task gets alot harder. Can be done, but you'd have to write functions to navigate through the records so it could know which is the selected record.
Mike
Upvote
0
I thank you all for your input. I do have unique records and my tables are normalized. If I were to create a query and put this line into the criteria it would work?
[Forms]![MyForm]![ID]
Of course I would put my form name and correct ID in the appropriate spots. Would this mean I would not need to use any Vbasic? Thanks.
[Forms]![MyForm]![ID]
Of course I would put my form name and correct ID in the appropriate spots. Would this mean I would not need to use any Vbasic? Thanks.
Upvote
0
Similar threads
