After Command Button, move to certain field

[summer]

I set up a command button on my form to duplicate the current record to a new record. The following is the event procedure that it created:

Private Sub dup_Click()
On Error GoTo Err_dup_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_dup_Click:
Exit Sub

Err_dup_Click:
MsgBox Err.Description
Resume Exit_dup_Click

End Sub

How would I add to this to tell it to go directly to the PartNumber field after it duplicates?

 

[David R]

At the risk of getting an answer, why are you copying the record?

Me.PartNum.SetFocus should do it though, after you go to the new record. I don't read DoMenuItem so I have no idea what those do. You might want to look in help for what the real commands are you're using.

 

[scottfarcus]

Private Sub dup_Click()
On Error GoTo Err_dup_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

PartNumber.SetFocus

Exit_dup_Click:
Exit Sub

Err_dup_Click:
MsgBox Err.Description
Resume Exit_dup_Click

End Sub

 

[summer]

I have 11 fields. The first 6 may be the same in the next record. So the easiest way I could figure to copy those 6 to the next was to put a duplicate command button. Then, the 7th field, Part Number, is where the data may change, so I want the cursor there.
I'll try what you both suggested. Thanks.

 

[summer]

When I put PartNumber.SetFocus in the string, I get an error that says Object Required.

If I put Me.PartNumber.SetFocus I get Compile Error: Method or data member not found.

Any ideas?

 

[David R]

Hmmmm. What 6 fields are you storing in every record? It's possible you don't need to do so. (It's possible you do, if they're only duplicate because the records are being entered one after the other).

I think we need a better picture of your data.

 

[David R]

Me.[PartNumber].SetFocus

the PartNumber part will change to whatever the name is of the control you want to set the focus to. I didn't know the name you used on your form (could be txtPartNo or Text075 or whatever).

 

[summer]

Here's the situation...
We get a debit from a customer. We have to enter these onto a spreadsheet (which I am re-creating to a database).

Fields are MRA, CK#, Cust#, Invoice#, Debit Amount, Date, Part Number, Item Number, Qty, Cost, and DMR#. The debit will usually have many part numbers which in turn reflects different Item numbers, qty, cost and DMR, but the first 6 listed above will be the same.

Is there an easier way to do this? Eventually, it has to be printed in a report.

Your last suggestion
Me.[PartNumber].SetFocus did not work. It says it can't find the field '|' in my expression. (???)

 

[David R]

You're going to have two tables (at least). Spreadsheets work differently than databases, which do not like to duplicate information. Because a relational database (Access, for example) can relate entries on one table to another, you can enter the MRA, CK#, Cust#, Invoice#, Debit Amount, Date, once for a record and then enter subrecords detailing the Part Numbers, Item Numbers, Qtys, Costs, and DMR#s. The main table (the one you have now) will have those first six fields. The subtable will have the latter grouping, plus a field of the same type as the identifier field (MRA? CK?) from your main record (whatever uniquely identifies that record - it may be more than one field). So if the identifier is an autonumber, it needs to be Long Integer. If it's Text, it needs to be text.

Go into Tools>Relationships and draw a join between the two matching fields in your tables. Check Referential Integrity and Cascade Update, Cascade Delete. You should end up with a One-to-Many relationship.

Now if you build a subform with these "repeating" fields and move them off your main form, you can go to one record once, enter the identifying information, and then go to the subform and enter all the parts/items/whatever you need to. Make the subform a Continuous Form probably.

Further addendums..addenda?:
You have a field called Debit Amount, and then one in the repeating fields called Cost. Is the Sum of Cost = Debit Amount? If so, you may not need to store it in the main record.
Field names with special characters besides the underscore in them can give problems. Access is more forgiving than most database systems, but consider renaming things like CK#, Cust#, Invoice#, etc. CustNo would be a good substitute, or CustNum/CustomerNumber.
Sorry the SetFocus thing didn't work out. However if you structure your data properly you shouldn't need to use it anymore.
Look up an article, book, or topic here in the forums about 'database normalization'. Especially if you're coming from a spreadsheet background, this will be invaluable to let you know how to work around these problems properly.
Your report can have subreports if you need to detail these sections. I've even used my subform from my form as the subreport and it comes out nicely if I have it formatted properly.

Good luck,
David R

 

[summer]

WOW! Thanks for the great information. I'll give it a try and report back to you.
Thanks again! Your help is much appreciated!

 

[summer]

David,
This is working great! You were right about the debit amount field - it is the sum of the costs.
I put an unbound text box on the form to calculate this amount. For the control source I have:

=Sum([Subtable Subform].Form!Cost)

My result is #Error.

Am I doing something wrong?

 

[David R]

Try =DSum("[Cost]","subtableName","[ForeignKeyFieldFromSubtable] = " & Me.NameOfPrimaryKeyControl)

This assumes the Primary/Foreign Key is numeric. Add single quotes around it if it is a text field.
=DSum("[Cost]","subtableName","[ForeignKeyFieldFromSubtable] = '" & Me.NameOfPrimaryKeyControl & "'")

An easier way would be to use the Form Footer/Header from your subform, and make a box = Sum([Cost])

[This message has been edited by David R (edited 04-25-2002).]