Using command button to add new record and set values to hidden fields

[jobrien4]

I have a form (frm_Parts) where the user will input part number, reivision number, and customer number and the record of that part comes up in the form. If this part has a bevel operation, a button with "Show Bevel Info" appears.

Upon clicking that button, frm_BevelInfo is launched in a new tab using an OpenForm macro (where the part number, revision number, and customer number from the first form equal the same fields in frm_BevelInfo). I set this table up as multiple items tabular form, because the same part can have multiple bevel operations that I want to all show up at once.

Here is my problem/what I want to accomplish. If the part from the frm_Parts doesn't have a bevel operation already, when frm_BevelInfo is launched, there is no way to add a new field.

Also, I want to have the Part Number, Reivision Number, and Customer Number hidden on frm_BevelInfo since this info is redundant to frm_Parts. However, when the user clicks an "Add New" button, I want to have these fields set to whatever the info is in frm_Parts.

I tried to use a
GoToRecord (New),
SetValue - [PartsNumber] - Forms![frm_Parts]![PartNumber]

But it doesn't work. Anybody have any thoughts?

 

[vbaInet]

I tried to use a
GoToRecord (New),
SetValue - [PartsNumber] - Forms![frm_Parts]![PartNumber]

It doesn't work? Where did you get this code from?

 

[jobrien4]

I set an embedded Macro to the command button with the following:

GoToRecord -> Object Type: Form, Object Name: frm_BevelInfo, Record: New
SetValue -> Item: [PartNumber], Expression: [Forms]![frm_PartDatabase]![PartNumber]
SetValue -> Item: [RevisionNumber], Expression: [Forms]![frm_PartDatabase]![RevisionNumber]
SetValue -> Item: [CustomerNumber], Expression: [Forms]![frm_PartDatabase]![CustomerNumber]

I get the following error when I open frm_BevelInfo and click the Add button:

"You can't go to the specified record. You may be at the end of a recordset"

 

[vbaInet]

Ah, macros. I forgot they exist. I don't have Access at the mo to be able to tell you the right commands. Perhaps someone else will be able to help in the meantime.

 

[jobrien4]

VBA is the next two chapters in the Access book I'm reading. Maybe I should go ahead and read those chapters...

 

[vbaInet]

VBA is the next two chapters in the Access book I'm reading. Maybe I should go ahead and read those chapters...

That's a very good idea

To move to a new record using VBA:

DoCmd.RunCommand acCmdRecordsGoToNew

To set the value of one field or control to the value of another field or control:

Me.Textbox1Name = Me.Textbox2Name

This will set the Textbox1Name to the value of Textbox2Name. Assignment is from right to left.

 

[mcktigger]

Don't ever use macros but try this. On the on click event of your cmdbutton put the code

DoCmd.GoToRecord , , acNewRec
me.PartsNumber = Forms!FrmParts!PartsNumber
me.RevisionNumber = Forms!FrmParts!RevisionNumber
me.CustomerNumber = Forms!FrmParts!CustomerNumber

this will set the fields in the current open form to those contained in FrmParts

 

[vbaInet]

Don't ever use macros...

Nothing wrong with it. It's the future.

 

[mcktigger]

Sorry just re-read my post. Meant to say

I don't ever use macros.

If they work for you then go for

 

[vbaInet]

Sorry just re-read my post. Meant to say

I don't ever use macros.

If they work for you then go for

I don't use them either But from what I hear Microsoft is looking to get rid of VBA from Access and use Macros instead. It's evident in Access 2010.