Textbox Search

[MCCDOM]

Hi all,

Hope you all had a good Christmas.

I am trying to create a form from which I can search for details based on a 'StockID' number. I have a 5 textboxes and 1 button. I want to be able to enter a StockID number into the first textbox and hit the search button which will then populate the 4 other textboxes with the related data linked to that StockID number. 3 of these textbox values will need to be pulled from different tables which are linked in a relationship.

Many thanks

MCCDOM

[orange]

And ????
You get errors? Number?

What exactly are you expecting as a response?

It would help if you :
-gave us a description of your environment,
-told us a little of your experience/expertise with database and Access,
-told us the issue you are having in plain English
-provided a jpg of your tables and relationships.

Are you asking whether or not this can be done?

[MCCDOM]

My apologies for not being clear in my first post.

What I am looking to do is create a stock finder that will search based on a 'Stock ID' number. It needs to search in two tables (tblStock and tblAllocated) and retrieve the appropriate data i.e. if the product linked to the Stock ID number has an 'AllocationID' to show allocation details and stock details in the designated fields on the form. If no 'AllocationID' is linked then only the stock details will be shown.

I'm fairly new to creating databases so my knowledge isn't that extensive so help would be very much appreciated.

I have attached screenshots of my layout so far and what I have in the way of tables and relationships.

[June7]

Why don't you have form/subform arrangement? Main form bound to tblStock and subform bound to tblAllocated. Find the StockID record on main form and any related allocation records will display in subform.

The Allocated yes/no field is not really needed. This can be calculated.

[MCCDOM]

Thanks June7 that's a great idea. Didn't know that you could do that. How could I get a search function for the stock ID though by using a button and textbox? Little bit stuck.

Many thanks

[MCCDOM]

I've managed to do a textbox search by using a macro on the 'on click' button function with the statement: Where Condition =[Stock ID] Like "*" & [Forms]![frmStockFinder Main]![Stock ID] & "*"

What I would like to achieve now is to have all the fields empty when you open the form instead of showing the first record details. Also is it possible to change the appearance of a subform. I would like to remove the parts circled in red on the attachment. Any ideas?

Many thanks

[June7]

Yes, you can disable the form Navigation Bar and Record Selector. These are property settings of the form.

If you want all the fields empty and still allow navigating existing records, move to new record row on main form. This can be done with code in the Open event.

DoCmd.GoToRecord , , acNewRec

[MCCDOM]

Thanks that worked for the main stock form but the subform of allocations displays (new) in the Allocation ID textbox as this is the primary key for the table. Is there now way to set the value as null?

[MCCDOM]

Or could I get the subform to be set to .visible = false when the Allocation ID box (txtAlloacatioID) is empty but when a value is present it changes to .visible = true

[June7]

New record row will always show (NEW) for an autonumber field until a record is initiated by data entry into another field.

Can manage Visible property of the subform container control with code. Use the main form OnCurrent event. Should not be dependent on value of any field in the subform. Make subform visible only after record initiated on main form or if viewing existing record.

[MCCDOM]

Thanks for that suggestion. My coding knowledge isn't the most extensive so what would I be looking to put in code wise to set off the visibility based on record initiation?

[June7]

Perhaps the AfterUpdate event of Item textbox:

Me.SubformContainerControlName.Visible = True

And in the form Current event:

Me.SubformContainerControlName.Visible = Not IsNull(Me.Item)

[MCCDOM]

Thanks for the help, all is working well now.