Find a record and append it as a new record with a macro

[Naresh Kumar Saini]

I have some experience in creating Tables, Quries, Reports and Forms but no
knowledge of VBA. I am experimenting with Macros for the first time. My Table
is simple -- no relationship, just a single table. For data entry I use a
Form. I frequently need perform a sequence of action on this Form:

(1) Find a Record (based on ID field)
* For this, click on ID control, press Ctrl-F to open Find and Replace box
and input required ID (and also set other options if they are incorrect from
some previous session -- 'Match' and 'Search'; 'Match Case' is not important
here.
(2) Click on Find Next, then Cancel, then select the Select Record with
mouse/Edit menu. Then click on New Record Button (so whole record is still
selected) and Press Control-V to paste the record there.
(3) Finally click on a particular control that would be changed now.

I think a Macro can do these actions more efficiently. For this I added a
Button on my Form and I am editing the 'On Click' Properties of this button.
I am stuck at:

(1) How to obtain user input for the Find box (Record ID to be found)
(2) How to Select Record (Edit Menu or Shift+SpaceBar)

I am sure there must be some ways to perform these actions without using
VBA. I hope someone can help.

 

[Naresh Kumar Saini]

I have since solved the problem. Here are the steps:

(1) On the Form I put a button called "Find and Append Record".
* The On Click Property of this button run a macro called
'FindAndAppendRecordParent'

(2) Content of 'FindAndAppendRecordParent' are as below:

(a) Action: GoToControl
Control Name: [ID]
----This would place the control on the desired Control that we would search
for.

(b) Action: OpenForm
Form Name: frm_FindAndAppendRecord
View: Form
Filter Name: [blank i.e. empty]
Where Condition: [blank i.e. empty]
Data Mode: [blank i.e. empty]
Window Mode: Dialog

----This Form called from_FindAndAppendRecord was created before hand. I
also set its property to Hidden so it is not visible normally. (see below)

****************************************************
This Form has only three things on it -- 1. brief instructions (optional) in
a text Lable -- 2. One Unbound text box called 'FindRecordID' to input thre
RecordID to find -- 3. A button called 'Proceed with Find and Appen Record'.
The On Click Property of this 'Proceed ....' button (as also the On Exit
Property of Unbound Text Box) run another macro called 'FindRecord_Append'.
The content of this macro are:

* Condition: [Forms]![Name_of_the_Form]![FindRecordID] Is Null
Action: MsgBox
Message: Please enter the RecordID to find
Beep: Yes
Type: None
Title: Blank
* Condition: ...
Action: StopMacro
* Condition: [blank i.e. empty]
Action: SetValue
Item: [Forms]![frm_FindAndAppendRecord].[Visible]
Expression: False
-----Note: This Form will take user input for RecordID to be searched and
make itself invisible yet remain available for rest of the macro that resumes
below
********************************************************

(c) Action: FindRecord
Find What: =[Forms]![frm_FindAndAppendRecord]![FindRecordID]
Match: Whole Field
Match Case: No
Search: All
Search As Formatted: Yes
Only Current Field: Yes
Find First: Yes

(d) Action: RunCommand
Command: SelectRecord

(e) Action: RunCommand
Command: Copy

(f) Action: RunCommand
Command: PasteAppend

(g) Action: GoToControl
Control Name: [The Control that would need to be changed now]

(h) Action: RunCommand
Command: SaveRecord

(i) Action: Close
Object Type: Form
Object Name: from_FindAndAppendRecord
Save: No
------Note: The Form that we made invisible after taking user input for
RecordID to be searched is closed so that next time the 'Find and Append
Record' button on our main Form is clicked, it would open with a blank
'FindRecordID' control

(j) Action: Beep


Hope this is helpful

Naresh Kumar Saini

 

[Naresh Kumar Saini]

I have since solved the problem. Here are the steps:

(1) On the Form I put a button called "Find and Append Record".
* The On Click Property of this button run a macro called
'FindAndAppendRecordParent'

(2) Content of 'FindAndAppendRecordParent' are as below:

(a) Action: GoToControl
Control Name: [ID]
----This would place the control on the desired Control that we would search
for.

(b) Action: OpenForm
Form Name: frm_FindAndAppendRecord
View: Form
Filter Name: [blank i.e. empty]
Where Condition: [blank i.e. empty]
Data Mode: [blank i.e. empty]
Window Mode: Dialog

----This Form called from_FindAndAppendRecord was created before hand. I
also set its property to Hidden so it is not visible normally. (see below)

****************************************************
This Form has only three things on it -- 1. brief instructions (optional) in
a text Lable -- 2. One Unbound text box called 'FindRecordID' to input thre
RecordID to find -- 3. A button called 'Proceed with Find and Appen Record'.
The On Click Property of this 'Proceed ....' button (as also the On Exit
Property of Unbound Text Box) run another macro called 'FindRecord_Append'.
The content of this macro are:

* Condition: [Forms]![Name_of_the_Form]![FindRecordID] Is Null
Action: MsgBox
Message: Please enter the RecordID to find
Beep: Yes
Type: None
Title: Blank
* Condition: ...
Action: StopMacro
* Condition: [blank i.e. empty]
Action: SetValue
Item: [Forms]![frm_FindAndAppendRecord].[Visible]
Expression: False
-----Note: This Form will take user input for RecordID to be searched and
make itself invisible yet remain available for rest of the macro that resumes
below
********************************************************

(c) Action: FindRecord
Find What: =[Forms]![frm_FindAndAppendRecord]![FindRecordID]
Match: Whole Field
Match Case: No
Search: All
Search As Formatted: Yes
Only Current Field: Yes
Find First: Yes

(d) Action: RunCommand
Command: SelectRecord

(e) Action: RunCommand
Command: Copy

(f) Action: RunCommand
Command: PasteAppend

(g) Action: GoToControl
Control Name: [The Control that would need to be changed now]

(h) Action: RunCommand
Command: SaveRecord

(i) Action: Close
Object Type: Form
Object Name: from_FindAndAppendRecord
Save: No
------Note: The Form that we made invisible after taking user input for
RecordID to be searched is closed so that next time the 'Find and Append
Record' button on our main Form is clicked, it would open with a blank
'FindRecordID' control

(j) Action: Beep

Hope this is helpful.

Naresh Kumar Saini