Form using Macros

[Srin]

Hi, I have a database in which I need to create a form. The form is opened by making a selection from another form which is a catalog that contains a number of available products.


For Ex: If the products were
Soap
Shampoo
Conditioner
Paper towels etc

And I happen to click on "Shampoo", then I am taken to another form that contains general information on the product. This form contains general info on the product such as availability, general comments about the product, who is the manufacturer and where it is manufactured.

I want to create a form to display the "Availability" status of the product which can be found in a table called "tblProdTimeline" in the database that has a column called "AvbStatus". I also want to display the general info below the "Availability Status" based on what it holds. Such as

If the Availability status is "Shipped",
info such as "Batch Size and No:
Shipping Date:
Inventory:" etc.


If it is "In Stock", then
info such as "Batch Size and No:
Available Inventory:
Next Shipment arrival date:" etc

to be displayed.

I also want to include a button to be able to edit these fields based on the Availability status. I have already created forms with these fields for each status, but am not sure of how to integrate it with the general Product Information form.

I may not be able to upload the database for review since it is company info. Please let me know how to go about this task. Thank you.

[ranman256]

when you click 'shampoo', you are taken to 'general info' screen.
on this Master form put 2 sub-forms (each has a different query)
sub1 InStock
sub2 Shipped

build 2 queries to get this data
qsInStock for sub1
qsShipped for sub2

build the sub1 form, frmInStock-sub, connect to qsInStock
build the sub2 form, frmShipped-sub, connect to qsShipped

now on the MASTER FORM, put the 2 subforms on it, and set
LINK MASTER FIELDS -on the master form, the key, (shampoo?)
and
LINK CHILD FIELDS -on the child form, the key, (shampoo?)

set both sub forms linking fields. Now you should see the shipped, and in stock records.

[Srin]

If there are 100s of products, will I have to link the master and child form for each product?
It maybe a noob question but please bear with me.

Thank you.

[JeroenMioch]

Hi Srin,

First of all you need to bound your products to ID's. I will explain why ;
Like your supermarket uses barcodes to identify their products, it returns an identification number on scanning the product.
It the scanner would return "Appels" and "Pears" rather then the identification number there would be problems when you have more then one sort of each product. Also, when you make one little mistake in the code you would get Applesause
when clicking an Apple.

Having said that, the records in your table should look something like this :

ProductID | ProductName | ProductBrand | ProductSupplier | WeightOrVolume | Ect | Etc

So your table contains all information about your product, and the product is identiefiable by its (unique) ProductID.
The productID should be always with your products name on your main form, visible or not but it has to be there.

To open a new form and immediately display the product you need to work with OpenArgs (Open arguments).
The argument is -off course- the ProductID and also present on the form your opening.

Lets first see if you understand what i mean and proceed from there on. I dont know what your knowledge of MS-access and Visual Basics is.

EDIT : I see someone is allready helping you. There are more approaches to achieve the same goal

[Srin]

Hey, I understand what you mean and my table has a Unique ID for each product. I was looking for a means to do it. If you could tell me how to do it via forms, then it would be helpful. Thank you.

[JeroenMioch]

Opening the new form and passing the ProductID is pretty easy, like this :

In my example im using an ObjectID in stead of ProductID. This is because im dealing with realty rather then products. But just adjust the code to your situation.

If Me.fldObjectID <> "" Then


Dim sWhere As String
sWhere = "[ObjectID] = " & Me.fldObjectID


DoCmd.OpenForm "YourFormToOpen", acNormal, , sWhere


Else
MsgBox "Select an object"
End If

Now edit your new form that holds the product information. Right click the top left corner of the form and select properties. And then the "Form open" event. select event procedure to input the code below :

Me.LocationName = DLookup("NameOfObject", "tblObjects", "[ObjectID] = " & Forms![YourFormToOpen]![fldObjectID].Value & "")

Edit to your situation, So LocationName is ProductName, tblObjects would be the name of your table. I think you get it. If not, let me know.

[Srin]

Hi, I donot know how to use codes. I instead use the Macro builder. Could you tell me how to do it using the Macro builder or where to navigate to use the codes.