GoToRecord subform - duplicate FK

A

adbie

Registered User.
Local time
Today, 09:02
Joined
Jul 1, 2009
Messages
19
Hi,

I'm a student and try to get a better understanding of VBA in an Access database.

I like to do the follow:

- Click in the popup box (LookupBatchViaProduct on the main form productselection) on a specific batchnumber and go to the specific record on the main form (form productselection)
I currently tried the following code but it doesn't work (in frpLookupBatchViaProduct):
Forms!frmProductSelection![frmBatch Subform]!BatchId.SetFocus = Me.BatchID

- Duplicate a complete batchnumber row include foreign key's like physical charact, .... (button function).

Best Regards
 

Attachments

I don't think you will be able to got to a specific record on your subform in one go.

First you will have to set the focus to the field in the subform, check out this link for the correct syntax for referencing Subforms and their objects.

Once there you will have to us DoCmd.GoToRecord to goto to the specific record you are looking for.
 
I tried it but does not work.
Maybe because it's a popupfrm and subform relation?
 
Can you please explain step by step what you want to happen.

ie. Step 1 User presses button x located on form y.
Step 2 something happens
Etc.
 
John Big Booty said:
Can you please explain step by step what you want to happen.

ie. Step 1 User presses button x located on form y.
Step 2 something happens
Etc.
Click to expand...
Step by step
1. User Open form frmProductSelection
2. User press action button 'Go to a specific Batchnumber' (popup frmLookupBatchViaProduct opens - not created yet in frmProductSelection)
3. User click on a specific record in the popupform (frmLookupBatchViaProduct) and click on 'go to product'
4. The application go to the specific record on the main and subform (frmProductSelection) based on the criteria BatchId
 
adbie said:
Step by step
...
2. User press action button 'Go to a specific Batchnumber' (popup frmLookupBatchViaProduct opens - not created yet in frmProductSelection)
......
Click to expand...

OK. What you need to do is create this form and implement my suggestion from Post#2 with the variation noted in Post#5.
 
John Big Booty said:
OK. What you need to do is create this form and implement my suggestion from Post#2 with the variation noted in Post#5.
Click to expand...
Thanks for your help,
But i'm afraid, I can't find the code that has to be put in the subform.
(In the attached file (post 1) is the frmLookupBatchViaProduct incude, only the action button link is not set.
 
The code you will need should be on a button on your Pop Up form it will look something like;

Code: 
Forms!Mainform!Subform1.Form!ControlName.SetFocus
DoCmd.FindRecord Me.ItemID, , , , , ,True
DoCmd.Close acForm, "PopUpFrm_Name"

See this link for an explanation of the DoCmd.FindRecord
 
Thanks alot for the code,
But now I get an debug error
(Run-Time Error '2110' Microsoft Office can't move the focus to the control Batchid):
I used the following code in frmLookupBatchViaProduct
Private Sub cmdLookup_Click()
Forms![frmProductSelection]![frmBatch Subform].Form!BatchId.SetFocus
DoCmd.FindRecord Me.BatchId, , , , , , True
DoCmd.Close acForm, "frmLookupBatchViaProduct"
End Sub
Click to expand...
 
Last edited:
It would appear that BatchID is not a control you have on that SubForm, so of course Access can not set focus on a non existent field.
 
I think the code you listed is going to have a problem because you are using

DoCmd.FindRecord Me.RecordID

Which is wrong if you are trying to find a record on the subform. You would be needing

DoCmd.FindRecord Forms![frmProductSelection]![frmBatch Subform].Form!RecordID ...etc.
 
John Big Booty said:
It would appear that BatchID is not a control you have on that SubForm, so of course Access can not set focus on a non existent field.
Click to expand...
Hi,

k thanks for the 2 reply's.
But how do I define a controle to batchId then?
 
Actually, I think my answer may have been wrong. I don't use the DoCmd.FindRecord much. So, ignore what I said.
 
adbie said:
Hi,

k thanks for the 2 reply's.
But how do I define a control to batchId then?
Click to expand...

Firstly it has to be part of the Record Source of your SubForm, then you need to have a control on the Subform that uses BatchID as it's Control source, then you will be able to SetFocus to it.
 
OK, having spent the better part of the morning trying to get this working I've finally succeeded, and learned something along the way.

For the DoCmd.FindRecord to work, it is imperative to ensure that the form to be searched has focus before this command is run, other wise you will get a run time error.

So the code that I gave earlier in this thread is doomed to failure, as the form initiating the search was not closed until the end of the code therefore the form to be searched did not have focus when the DoCmd.FindRecord was run.

Modified code should look like;

Code: 
Dim SrchVar As String

[COLOR="SeaGreen"]'A logical test here to ensure that ItemID hold a value is a good idea
[/COLOR]
SrchVar = Me.ItemId

DoCmd.Close acForm, "PopUpFrm_Name"
Forms!Mainform!Subform1.Form!ControlName.SetFocus
DoCmd.FindRecord SrchVar, , , , , ,True
Lesson learned; Command order is very important to get the correct, and expected, response from your code
 

Attachments

Last edited:
Thanks alot for your great help!
And do you know a solution for the second problem?
I like to duplicate a full row
why? Sometimes I have to create a new batch that looks similar to one I created earlier, so I can simply duplicate it and edit the necessary things like other productiondate (Copy Parent Record and Child).
 
Last edited:
John Big Booty said:
I think the amendment to the DB will do what you are looking for.
Click to expand...
Hey,

Yes It looks good,
but when I select the flagged items and press copy he duplicate in the same country record and it has to be include in a new Country Record Like Andorra15, AD2 (so I can change the country code and productcode if I want)
 
OK. Seems I've misunderstood, slightly. It should be fairly straight forward to organise.

What you need to do is to open a pop up form that has a Combo box that includes all the countries (Clients, whatever) that the flagged record is not currently associated with, then use the result of that combo to append the flagged recorded to the correct country. Shouldn't be too hard with what's already in the sample DB.

Let me know how you get on.
 
You must log in or register to reply here.

Similar threads

S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
390
SkeletorPlus
S
R
How do I save the layout of a subform after modifying it on the main form's on open event?
Replies
12
Views
371
RickSanchez
R
E
Solved Refer to Subform control
Replies
9
Views
483
cheekybuddha
cheekybuddha
C
Subform Footer Textbox wont update
Replies
11
Views
448
Pat Hartman
P
I
Make Table from filtered subform
Replies
4
Views
429
mike60smart
mike60smart

Users who are viewing this thread

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