From Form to Form with a Button

[TheNovice]

Good Afternoon everyone,

I have a simple question (I HOPE), i am designing a form that has a Sub form
with Product Information.

I want to put a Button that when I select the product (button) it will
automatically transfer the Product number to Order detail (Product ID,cost,
etc)

here is how it is designed,

I have a Form and then a Sub form with Order Info, and another form with
product information.

Any Ideas would be Awesome.

 

[Jeanette Cunningham]

Hi TheNovice,
I suggest changing the steps and starting with the Order detail.
When doing a new line in the order detail form, use a combo box to show all
the products and choose one product from the combo.

The row source for the combo will be a query using the table for Products.
Include the ProductID in the query as the first column of the query.

So you will have 2 columns in the query.
Make the combo box have 2 columns.
Set the width of the first column to 0 to hide it from the user.
The user sees only the product name, but the database saves the ProductID in
the order detail table.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[TheNovice]

Jeanette,

Thanks for the quick reply, my initial design was to do that and it would
have required far less programming on my end. when it was submitted they
made the change request to easily go through a list catagorized by brands.
This is a Sales Entry Tool, and to have to go through 100's of Line Items
would take a while, and this will give the sales force an opportunity to sell
additional items without having a Products list on paper etc.

Any help would be greatly appreciated.

P.S. hows the weather in Australia?

 

[Jeanette Cunningham]

The principle still applies that the product is an entry on the line item of
an order detail form.

Here is what I have done in a similar situation.

Instead of a combo box to choose a product, I have a button that opens a
product search form in continuous view with search drop downs and text boxes
at the top of the form.

There are dropdowns for Category, Brand, Sub category, CodeNumber and
others.
This makes it quick and easy for users to pick the product they need.

Once user has selected the product by double clicking the one they want, I
have code that puts that product into the order details form together with
other pertinent info about the product such as brand, price etc. and at the
same time closes the product search form.

When they get to the next line of the order detail form, they click the
button to open the product search form again and choose a different product.

The weather in my part of Australia is warming up and spring is on the way.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Sorry to hear the fires are bad. We had bad fires 50 km from here last
summer. Many lives were lost in the Black Saturday fires.

Here's the code that opens the form where users can choose a part - Note:
parts instead of products. If user is editing the part for this line of the
order detail, the code opens the search form at that part. If user is on a
new record, it opens the search form ready to search for a part.

The code opens the search form in dialog mode so that user is forced to
either choose a part or cancel.
After the user makes their selection the code back in the order detail form
grabs the needed information, closes the search form and continues on to put
the details for the part in the order detail form.


----------------------------
Private Sub cmdGetPart_Click()


Dim strForm As String
Dim frm As Form
Dim lngPartID As Long
Dim strCode As String
Dim strPartCat As String
Dim strBrand As String
Dim strPartDescr As String
Dim strPartSubCat As String
Dim strPartSubSub As String
Dim curCost As Currency
Dim curSell As Currency
Dim dblMarkup As Double
Dim lngSOH As Long
Dim lngLen As Long
Dim strWhere As String

strForm = "frmSearchPartJobSale"

If Len(Me.PartID & vbNullString) > 0 Then
strWhere = "[PartID] = " & Me.PartID
End If

If Len(strWhere & vbNullString) > 0 Then
DoCmd.OpenForm strForm, WindowMode:=acDialog,
WhereCondition:=strWhere
Else
DoCmd.OpenForm strForm, WindowMode:=acDialog
End If

If Len(Me.PartID) > 0 Then
Me.Undo
End If

With Forms(strForm)
lngPartID = .ThePartID
If lngPartID > 0 Then
curCost = Nz(.PriceEachEx, 0)
curSell = Nz(.SalePriceEach, 0)
dblMarkup = Nz(.Markup, 0.25)
strPartCat = Nz(.PartCat, "")
strBrand = Nz(.Brand, "")
strPartDescr = Nz(.PartDescr, "")
strPartSubCat = Nz(.PartSubCat, "")
strPartSubSub = Nz(.PartSubSub, "")
strCode = Nz(.Code, "")
lngSOH = Nz(.TotOH, 0)
End If
End With
DoCmd.Close acForm, strForm

If lngPartID > 0 Then
Me.PartID = lngPartID
If curCost > 0 Then
Me.PriceEachEx = curCost
End If
If curSell > 0 Then
Me.SalePriceEach = curSell
End If
Me.MarkupUsed = IIf(dblMarkup > 0, CStr(dblMarkup * 100) & "%",
Null)

If Len(strPartDescr & vbNullString) > 0 Then
Me!PartDescr = strPartDescr
End If

If Len(strPartCat & vbNullString) > 0 Then
Me!PartCat = strPartCat
End If

If Len(strBrand & vbNullString) > 0 Then
Me!Brand = strBrand
End If

If Len(strPartSubCat & vbNullString) > 0 Then
Me!PartSubCat = strPartSubCat
End If

If Len(strPartSubSub & vbNullString) > 0 Then
Me!PartSubSub = strPartSubSub
End If

If lngSOH > 0 Then
Me.PartID.Tag = lngSOH
Else
Me.PartID.Tag = ""
End If

If Len(strCode & vbNullString) > 0 Then
Me.Code = strCode
End If

If Len(Me.Quantity & vbNullString) > 0 Then
Else
Me.Quantity = 1
End If

Else
Me.Undo
End If


End Sub