Command Button using If/Then (1 Viewer)

Endojoe

Endojoe

Registered User.
Local time
Today, 06:01
Joined
Apr 7, 2009
Messages
20
I'm trying to use an If/Then statement to get a command button to create a new record with certain information carried over from an existing record, but just exactly what information is carried over is dependent upon the selection present in an Option Group...each half of the code works fine by itself, but when I put it into an If/Then (as below)...the button fails to function. (There are substantially more fields I'm actually carrying over, but it seemed redundant to have like 40 more lines in the code box)


Code: 
Private Sub cmd_copy_record_Click()

If Me.opt_Assy_SubAssy = 1 Then

Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant

v1 = Me!txt_date.Value
v2 = Me!txt_TRACK_NUMBER_CERT.Value
v3 = Me!txt_CUSTOMER_NAME.Value

RunCommand acCmdRecordsGoToNew

Me!txt_date = v1
Me!txt_TRACK_NUMBER_CERT = v2
Me!txt_CUSTOMER_NAME = v3

ElseIf Me.opt_Assy_SubAssy = 2 Then

Dim v1 As Variant
Dim v2 As Variant

v1 = Me!txt_date.Value
v2 = Me!txt_TRACK_NUMBER_CERT.Value

RunCommand acCmdRecordsGoToNew

Me!txt_date = v1
Me!txt_TRACK_NUMBER_CERT = v2

End If

End Sub

So it's likely I'm missing something really simple, possibly even stupid, but I've looked at it over and over again and don't see why it shouldn't work...granted, I'm not particularly well versed in VB. Any suggestions would be wonderful. Thank you all in advance. :)
 
M

Mr. B

"Doctor Access"
Local time
Today, 06:01
Joined
May 20, 2009
Messages
1,932
Endojoe,

For one thing, you are declaring your variables twice.

Take out the:
Dim v1 As Variant
Dim v2 As Variant
after the "ElseIf" statement.

HTH
 
Endojoe

Endojoe

Registered User.
Local time
Today, 06:01
Joined
Apr 7, 2009
Messages
20
Mr. B thanks a million!! Put me on the right track. I took my Dim declarations out of the ElseIf and moved them all up to before I even began the If/Then statement and everything worked as I had wanted/expected it to. My complete working code for anyone interested in the future:

Code: 
Private Sub cmd_copy_record_Click()

Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
Dim v5 As Variant
Dim v6 As Variant
Dim v7 As Variant
Dim v8 As Variant
Dim v9 As Variant
Dim v10 As Variant
Dim v11 As Variant
Dim v12 As Variant
Dim v13 As Variant
Dim v14 As Variant
Dim v15 As Variant
Dim v16 As Variant
Dim v17 As Variant

If Me.opt_Assy_SubAssy = 1 Then

v1 = Me!txt_date.Value
v2 = Me!txt_TRACK_NUMBER_CERT.Value
v3 = Me!txt_CUSTOMER_NAME.Value
v4 = Me!txt_PO_NUMBER.Value
v5 = Me!opt_Assy_SubAssy.Value
v6 = Me!opt_Cert_Type.Value
v7 = Me!opt_Export_Domestic.Value
v8 = Me!Txt_Country.Value
v9 = Me!txt_STATUS.Value
v10 = Me!txt_SPU_LOT_NUMBER.Value
v11 = Me!txt_Export_Domestic_Cert_Verbage.Value
v12 = Me!txt_Assy_SubAssy_Cert_Verbage.Value


RunCommand acCmdRecordsGoToNew

Me!txt_date = v1
Me!txt_TRACK_NUMBER_CERT = v2
Me!txt_CUSTOMER_NAME = v3
Me!txt_PO_NUMBER = v4
Me!opt_Assy_SubAssy = v5
Me!opt_Cert_Type = v6
Me!opt_Export_Domestic = v7
Me!Txt_Country = v8
Me!txt_STATUS = v9
Me!txt_SPU_LOT_NUMBER = v10
Me!txt_Export_Domestic_Cert_Verbage = v11
Me!txt_Assy_SubAssy_Cert_Verbage = v12


ElseIf Me.opt_Assy_SubAssy = 2 Then


v1 = Me!txt_date.Value
v2 = Me!txt_TRACK_NUMBER_CERT.Value
v3 = Me!txt_CUSTOMER_NAME.Value
v4 = Me!txt_PO_NUMBER.Value
v5 = Me!opt_Assy_SubAssy.Value
v6 = Me!opt_Cert_Type.Value
v7 = Me!opt_Export_Domestic.Value
v8 = Me!opt_PMA_ASSY.Value
v9 = Me!Txt_Country.Value
v10 = Me!txt_STATUS.Value
v11 = Me!txt_MODEL.Value
v12 = Me!txt_DRAWING_REV.Value
v13 = Me!txt_FINAL_ASSEMBLY.Value
v14 = Me!txt_REQUEST_TYPE.Value
v15 = Me!txt_SPU_LOT_NUMBER.Value
v16 = Me!txt_Export_Domestic_Cert_Verbage.Value
v17 = Me!txt_Assy_SubAssy_Cert_Verbage.Value


RunCommand acCmdRecordsGoToNew

Me!txt_date = v1
Me!txt_TRACK_NUMBER_CERT = v2
Me!txt_CUSTOMER_NAME = v3
Me!txt_PO_NUMBER = v4
Me!opt_Assy_SubAssy = v5
Me!opt_Cert_Type = v6
Me!opt_Export_Domestic = v7
Me!opt_PMA_ASSY = v8
Me!Txt_Country = v9
Me!txt_STATUS = v10
Me!txt_MODEL = v11
Me!txt_DRAWING_REV = v12
Me!txt_FINAL_ASSEMBLY = v13
Me!txt_REQUEST_TYPE = v14
Me!txt_SPU_LOT_NUMBER = v15
Me!txt_Export_Domestic_Cert_Verbage = v16
Me!txt_Assy_SubAssy_Cert_Verbage = v17
End If

End Sub


This Command Button will duplicate the contents of the controls defined in the current record and carry them over into a new record. Which controls are duplicated is determined by the Option Group selection (which obviously must be selected prior to OnClick of the Command Button). Hope this thread can help someone else in the future. Thanks again Mr. B!! Fellow Texan I see, small world!! :D
 
SOS

SOS

Registered Lunatic
Local time
Today, 04:01
Joined
Aug 27, 2008
Messages
3,517
Looks to me that you are in serious need of normalization for your database. You should NOT be storing redundant data like this.
 
Endojoe

Endojoe

Registered User.
Local time
Today, 06:01
Joined
Apr 7, 2009
Messages
20
:D From your lips to gods ears...I wish I was the boss. I'm just trying to make my data entry job easier...and I'm lucky I have been given the OK to do even that much :) Things are done the way they have always been done and it has always worked... LOL Everything I'm entering is in at least two other databases already...and I get to enter it into our own DB off of a paper printout from the other databases...:eek: I'd like to thank everyone on this site for all the help ya'll provide, it's been invaluable to making my day to day work life much easier and less stressful. I have spent countless hours surfing through the forums here, and its rare to find a question that hasn't already been answered, and I'm sure my questions may have already been answered somewhere, but it's sometimes tough to find a very specific applicable answers to my situations, which, as SOS pointed out...are quite ludicrous in many instances, due to the 'establishment'. What I'm working with having been 'working' long before I started trying to work with it... :)
 
Last edited:
M

Mr. B

"Doctor Access"
Local time
Today, 06:01
Joined
May 20, 2009
Messages
1,932
Endojoe,

Only one suggestion that you might look into. You are declaringa variant variable in a way that can be done much simpler. Take a look at an Array. You would declare an array with as many elements as you need like
dim varMyVal(17) as variant
Then you can refer to any element in the array. The numbering of the elements in the array are zero based meaning that the first element would be zero and the second 1 and so on.
The following line would read the first element of the array.
Me.NameOfSomeControl = varMyVal(0)

The next line would read the second element
Me.NameOfSomeControl = varMyVal(1)

Using an Array can be cleaner and simpler to read.

Just my thoughts.

NTN
 
  • Like
Reactions: SOS
Endojoe

Endojoe

Registered User.
Local time
Today, 06:01
Joined
Apr 7, 2009
Messages
20
That looks like it will make for much cleaner code! And after some research on it, it would appear to be a very powerful tool for many other things too, thank you for the suggestion!
 
M

Mr. B

"Doctor Access"
Local time
Today, 06:01
Joined
May 20, 2009
Messages
1,932
You are welcome.

Sometimes you just need to look at other possibilities.

Good luck with you project.
 
You must log in or register to reply here.

Similar threads

B
Solved Filtering Subform based off carried over data
Replies
5
Views
328
Pat Hartman
P
N
Can't get query criteria to read a form text field
Replies
9
Views
427
Pat Hartman
P
C
Invalid Use of Null
2
Replies
20
Views
519
chuckcoleman
C
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
411
gemma-the-husky
gemma-the-husky
A
Solved Copying data from Quote-line-items Subform to Sales-Order-line-items Table : Error 3022
Replies
6
Views
248
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom