Looking up subform record from main form (1 Viewer)

stell

Registered User.
Local time
Today, 05:19
Joined
Jun 14, 2017
Messages
15
Hello,

I need some wisdom bestowed upon me. I am trying to set up a relationship between a form and a sub form in way that will let me look up and edit sub form records from the main form. I attached a picture that will hopefully make it clear.

Access_form.PNG

I want a user to be able to search for a form that needs to be updated. I.e., when a user hits the 'update shop order' button I want that form to be looked up and edited.

Attached below is the macro that I am currently trying to use, with no luck. If anyone can give me any advice on how to achieve this I would greatly appreciate it.

macro.PNG

Thank you
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:19
Joined
Jul 9, 2003
Messages
16,245
I think your question could generate a bit of confusion because subforms are usually for displaying multiple rows of related data, not a single data item. Although there's no reason why you shouldn't display a single item in a subform. However, I think in your particular case, you don't actually need a subform.

So I would like to rewrite your question as follows...

I have a textbox in which the operator types the shop order number. When the operator presses the command button the details of the shop order number entered in the text box are shown.

There are several ways this could be done.

If the shop order number already exists then it could be looked up in a combobox. The combobox would then update and display the related records on the form. This can be setup quite simply with the combobox wizard. (You would not need the Command button in this case)

You could apply a filter to the form using the information from the text box.

You can also use the information from the textbox to filter the form by updating the forms record source with an SQL statement created in VBA.
 
Last edited:

stell

Registered User.
Local time
Today, 05:19
Joined
Jun 14, 2017
Messages
15
I think your question could generate a bit of confusion because subforms are usually for displaying multiple rows of related data, not a single data item. Although there's no reason why you shouldn't display a single item in a subform. However, I think in your particular case, you don't actually need a subform.

So I would like to rewrite your question as follows...

I have a textbox in which the operator types the shop order number. When the operator presses the command button the details of the shop order number entered in the text box are shown.

There are several ways this could be done.

If the shop order number already exists then it could be looked up in a combobox. The combobox would then update and display the related records on the form. This can be setup quite simply with the combobox wizard. (You would not need the Command button in this case)

You could apply a filter to the form using the information from the text box.

You can also use the information from the textbox to filter the form by updating the forms record source with an SQL statement created in VBA.

Thanks for the response. The shop order number is being inputted via scanner, so I would like to avoid the combo box if possible since it would be a pretty long list and it would take more time.

Can you give an example of what that SQL statement might look like if I were to filter the form? VBA is not my strong suit. Or is it possible to do what I'm already trying to do by changing the path?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:19
Joined
Jul 9, 2003
Messages
16,245
Wether you use a combo or textbox, you will need the barcode scanner to emulate the pressing of the enter key.

I suggest you alter the after update statement for the text box to show a message when the after update event has run.

Barcode Scanning demonstrated in this YouTube:-

Barcode Scan - Nifty Access
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:19
Joined
May 7, 2009
Messages
19,175
This is not macro but vba, on the click event of the button add this code:


Private Sub ButtonName_Click()
Me.navigationsubform.SetFocus
DoCmd.SearchForRecord acActiveDataObject, , acFirst, "[shop orde] like """ & "*" & Me.Text9 & "*" & """"

End Sub
 

Users who are viewing this thread

Top Bottom