I am interested, is it possible to read currently selected (highlighted) row in table?
I know that this is possible to move cursor on concrete field in table via VBA but is it possible to read record number when we move cursor manually (via arrows)?
Use the Form OnCurrent event. Note that the "row number" for the same record can be (and it is) different for different sort orders. What is "constant" is the data in record's fields: The record ID is an example.
Keeping in mind what Mihail said about 'row numbers' changing, through sorting changes, as well as adding Records and deleting Records, here's a short step-by-step:
First you have to force Access to load the entire RecordSet, something the Access Gnomes don't always do when a Form first Loads/Opens:
Code:
Private Sub Form_Load()
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst
End Sub
Then to update the count as you move from Record-to-Record:
For aLabel
Code:
Private Sub Form_Current()
Me.LabelName.Caption = "Record " & CurrentRecord & " of " & RecordsetClone.RecordCount & " Records"
End Sub
For aTextbox
Code:
Private Sub Form_Current()
Me.TextboxName.Value = "Record " & CurrentRecord & " of " & RecordsetClone.RecordCount & " Records"
End Sub
Maybe it will be simpler if I try to explain what I am trying to do. At this moment I have a table with set of data. In some of them there are I can say some records which should be unique but they are aggregated into one row. I mean apart of 2 fields (ID included) everything is the same. I need to create a few records from that one. I need to do this manually because I need to verify if that row should be separated into few of them or not.
I would like to help myself via macro. First approach I tried was:
However I got the error that record cannot be copied now.
I found example code here or an another forum where it was done via copy recordset and Clone function. I needed to adjust the code to my needs and it looks:
Code:
Public Sub DuplicateRecord(strTable As String, intRecordNo As Integer)
Set db = CurrentDb
Set tbl = db.TableDefs(strTable)
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset(strTable)
rst.MoveFirst
Set rst_temp = rst.Clone
With rst
.AddNew
i = 0
rst_temp.Move intRecordNo - 1
For Each fld In rst.Fields
If fld.Name = strFieldName Then
fld.Value = "D_" & rst_temp.Fields(i).Value
Else
fld.Value = rst_temp.Fields(i).Value
End If
i = i + 1
Next
.Update
End With
db.Close
Set db = Nothing
IsOpen = SysCmd(acSysCmdGetObjectState, acForm, "TABLE_ADJUSTING")
If IsOpen Then
DoCmd.Close acForm, "TABLE_ADJUSTING"
End If
MsgBox "Ready"
End Sub
It works perfectly. I can modify records during process what is also very helpful as I can notice which records are duplicated by me and even if the order was changed I can recognize them. By this I can avoid duplicate of duplicate (in this case I gave "D_" at the beginning).
However this code is based on record number and by this I need current record number where I am currently to simplify the process. Then I won`t have to enter the row I would like to duplicate each time. The idea is, I am moving manually in the table via arrows and I stop at the row which I need to correct. Then I click the button and duplicate is done.
If you have any ideas I would be grateful. I know that table shouldn`t be operated directly without forms but this is only on my needs so this is not the problem.
Don't misunderstand, we're not saying that it is never correct to retrieve the record number, vis-à-vis the current RecordSet, just pointing out the transient nature of such a number!
I've run across this before, on Access forums, and I think the problem is related to your use of the Command
DoCmd.RunCommand acCmdPaste
acCmdPaste is intended to be used to Paste data from one place to another, typically from the Clipboard into a Control on a Form. To paste an entire Record into a New Record you would typically use
This last line needs to be corrected, even if it's not causing the current problem.
What event were you using to call this Macro? There are times, in the cycle of creating/saving a Record, when you simply cannot move to another Record.
This old saw about not operating directly in a Table, without using Forms, doesn't pertain to editing a Table through a RecordSet, which is what your last attempt does, it pertains to letting end users enter data directly into a Table, through the keyboard. Using RecordSets is perfectly OK, if a little labor-intensive, at times!
First, thanks for the reply. I didn`t have time to continue this topic but now I would like to find the solution in access. I`ve done it in excel (I exported the data, prepared macro in 5 minutes and it works). Finally when I finished duplicate I imported the table to access again so workaround exists. Why isn`t it so simple in the access?
What event were you using to call this Macro? There are times, in the cycle of creating/saving a Record, when you simply cannot move to another Record.
Generally the idea is to move cursor within table and when I find record I need to duplicate (it is highlighted) I would like to open form (via button) and click button on it to duplicate record. I can also select the whole record and using the same way (i.e. via form and button on it) duplicate the record.
I`ve done it in excel (I exported the data, prepared macro in 5 minutes and it works). Finally when I finished duplicate I imported the table to access again so workaround exists. Why isn`t it so simple in the access?
Some things work better/are easier to do in one software compared to another. However database is not spreadsheet. It's a little bit like why a Ford part doesn't fit on a Lada. The part may serve the same function (eg water pump) and even use a standard 7/16" or 8mm bolt, but the part is different. Same thing with Access and Excel.
They are different tools or different audiences/purposes. A few things you could do in either, but not everything.
I you are familiar with Excel and its functionality suits your purpose, then why use Access? I you plan on using Access to do exactly what you currently do in Excel, then don't change.
I've seen a lot of fortran programmers who use vba (and COBOL) just the way they did with Fortran( x,y,a1...).
I`ve done it in excel ...... Why isn`t it so simple in the access?
Why is a simple task to go at home and so difficult to find another address even in your town ?
I have used Excel more than 10 years before starting with Access.
Believe me. Access is more more powerful.
I'm happy that you solve your problem.
Can you post here your database and the results you expect ?
Of course, we need a sample not the real information if that violates some rules of confidentiality.
Ok, you didn`t understand me. This statement was meant to be like ironic suggestion: I thought I know VBA and I manage to do it in 5 minutes in excel but I can`t cope with it in access.
I know that access is totally different than excel as I have prepared some different functions in Excel and in Access.
Now I am trying to do another thing in excel which is also not typical I think. I would like to call vba function when I click the url. But ... I would like to url doesn`t redirect to page (so url is not open in the browser, however I would like to use follow_hyperlink event). I needed to create workaround too, finally I think I have the solution. But in another tool it would be simpler I think
I am using both tools (it depends on my need, what I need to do).
This topis concerns what I would like to create in Access. Now I have prepared such function in excel and it was enough. I find it practical if such function could be easy adopted to access (but with mechanisms that Access allows to be used).
In this statement I just mentioned that I know that these tools have its specific let me say 'behaviour' and built functions. Not everything is as I would like to it will be. So sometimes I need workarounds (as my needs are not fully tied with common functionality of such a tool).
The tools (Access/Excel) have different object models. My feeling is you should determine if you have a database application or a spreadsheet application, then make a decision.
However,you can continue to use both and you wouldn't be the first to use multiple tools to do a job. It usually takes some experience to recognize that workarounds to make a tool work are not a long term strategy. At some point the difference in the tools and their object models will become clearer and future projects will benefit.
The tools (Access/Excel) have different object models. My feeling is you should determine if you have a database application or a spreadsheet application, then make a decision.
However,you can continue to use both and you wouldn't be the first to use multiple tools to do a job. It usually takes some experience to recognize that workarounds to make a tool work are not a long term strategy. At some point the difference in the tools and their object models will become clearer and future projects will benefit.
I think it is a little in another way. This is I can say only one simple task, not the complicated project. I got the set of data and I need to adjust it to my need. I wouldn`t like to do it manually so I thought I can prepare macro and do it what I need. The input was as access database (but only data was important, not forms, functions and so on). I can do everything manually even in notepad but it seems to make no sense. Namely, this is not complicated project on 'real' database (as I mentioned in the first post this is only on my need, just simple solution not to repeat manually one task e.g. 1000 times).
Sometimes I have some simple tasks and I need to find the solution. I can even create C++ or C# program to achieve required output. But I know VBA f.e. and it is simpler to prepare such a 'small application' to parse the data and adjust them to required output.
This time I cope with the problem via excel. Nevertheless I assume that such a case can repeat in future (because: why not?) and then once prepared tool can be usefull again.
