Forms with Subforms

Solvi92

New member
Local time
Today, 09:55
Joined
Jan 27, 2014
Messages
2
Hi all,

I have good experience with Excel however it does not cater for everything I need to do and as such ahve recently ventured into access.

I have created a database that allows a user to enter data about damages on inbound loads to a warehouse. They enter the case ID, Haulier, Supplier ..... bla bla bla other boring information. They also enter the damaged items by product number and quantity. As I don't know the number of unique items that can be damaged, as many as 30 or 40 different product codes can be damaged per shipment I have created another table that relates the Case ID to each damage. Rather than creating a load of fields such as damage1 damage2 etc etc.

How this is making sense so far.

I have created a form with a subform that allows the user to enter all the information about the load and then the subform at the bottom in datasheet form so they can list all the product codes and quantities etc.

This form works fine, got some macro buttons and everything is looking fab.

However I need to create a form that allows the user to enter a case ID in a box, press a button and it pulls up all the information, including the damages for editing.

I've looked around and people suggest drop-down boxes etc but with over 1000 cases so far and more to come searching in a drop down list is a bit silly.

So I'm completely stuck on how to get started with my "Edit a Previous Case" form.

Any help would be greatly appreciated. I'm sorry its so long I just wanted to make sure I gave enough detail. Any other questions then please ask!

Many thanks,

Matthew
 
I would suggest a text box to be used as a search box (unbound) on the main form. Then a user can enter the search criteria and if you have a button (or use after_update event on the text box) you can refresh/run a query which populates the sub-form.

The sub-form query will need something along the lines in the criteria section (where clause):
Code:
Like "*" & [Forms]![form_name]![txt_search_caseid] & "*"
Where txt_search_caseid is the textbox you placed on the main form.
 
:confused: At the risk of sounding stupid, how do I build the event from the unbound text box. I have used vba in excel but I'm yet to attempt it in access..
 
As the form will load the button and sub-form at the same time. You should add it to the "After Update" event I imagine.

You can access this via the Property Sheet - > Event -> After Update section, select the ellipsis and choose code (or choose Event Procedure from the drop down and then select the ellipsos). Once the VB window opens it will pop you into the necessary sub and type:

Code:
me.requery

I guess that won't work so try:

Code:
 Forms![main_form_name]![sub_form_name].Requery

Good luck
 
If you used the ComboBox wizard to create a cbo on the MainForm and selected option #3 from the wizard. it would do what you want.
 

Users who are viewing this thread

Back
Top Bottom