Customers and Orders tables - simple relationship

[MikeLeBen]

Thanks for providing the 2000 version; I only have Access 2003 here at work (2010 at home).

The main thing I noticed is that you have the quantity field in the order_products table as text, it shoud be a number (long number if you do not expect fractional quantitites, otherwise single or double precision). After I changed the data type of the quantity field to a number, I deleted the subform and rebuilt it. It is working in the attached database.

Thank you, I'll follow your advice as I get to work tomorrow. Honestly, though, I think this is caused by an access bug or something, because I set that field as Text since that's what you had used for your example CustomerOrders.mdb.

 

[jzwp22]

My apologies, the quantity field should have been a number. I'm angry at myself for that one!

I thought at first that the module (FindAsYouTypeCombo) might have been at fault since in some of my initial testing when I deleted it, the form worked. But then I found the quantity datatype issue, so I re-unzipped your database and tried it with the module in place and was able to get it to work after correcting the datatype and rebuilding the subform.

 

[MikeLeBen]

No need to apolgize! In fact I'm surprised your version worked regardless of that issue, i think that appeals more to informagics than computer science but meh

 

[jzwp22]

I was surprised it worked too!

 

[MikeLeBen]

I tried this on my own, but as usual my lacking knowledge of VB prevented me from succeeding.

I am trying to add the button that will let me pop up the frmOrders for the selected record in the frmCustomer (the one with the search function you gave me a few days ago).

I tried with adding the button in the main form with this event

Private Sub cmdAddOrder_Click()

Dim strWhere As String
 If Me.Dirty Then Me.Dirty = False
 If Not IsNull(Me.custID) then
 strWhere = "custID = " & Me.custID
 DoCmd.OpenForm "frmOrders", WhereCondition:=strWhere
 End If

End Sub

and added a text box bound to the custID field in either the form or the subform, to no avail.

 

[jzwp22]

You will need a texbox on the order form to hold the customer ID and then populate it after the form opens

DoCmd.OpenForm "frmOrders"
forms!frmOrders.customerID=me.custID

the customerID is the customerID control on the order form. The me.custID refers to the customer ID control on the form that has the button you are clicking. (the me. is just a short hand notation for the current form).

 

[MikeLeBen]

I'm afraid since the button refers to a value stored in a subform, I need to somehow sync the value, or put the button in the subform itself.

 

[jzwp22]

I got confused there, are you trying to do this from the form that has the listbox on it? Is the form name Ricerca_Clienti ?

 

[jzwp22]

Yes, you will have to reference the subform. Your user will have to select the desired customer from the subform and then click the button to open the order form. I have added the functionality to your previous database and have attached it here.

 

[MikeLeBen]

Perfect solution, succesfully implemented and joy generating!

I'm building a new form named frm_NavOrdini in which the user chooses a date from the combobox named cboData and the listbox below (named lstOrdiniPerData) is populated with the orders for selected day.

The cboData has the following SQL as Row Source:
SELECT DISTINCT Ordini.IDOrdine, Ordini.DataOrdine FROM Ordini ORDER BY [DataOrdine];

Then user would be able to view the details of a given order by clicking on it in the listbox.

This is the VB code (not working of course ) I put in the afterupdate event of the cboData box:

Private Sub cboData_AfterUpdate()

   With Me![lstOrdiniPerData]
If IsNull(Me!cboData) Then
       .RowSource = ""
Else
       .RowSource = "SELECT [IDOrdine] " & _
                    "FROM Ordini " & _
                    "WHERE [DataOrdine]=" & Me!cboData
End If
     Call .Requery
   End With

End Sub

I get the following error: Syntax error in WHERE clause.

 

[jzwp22]

Based on this:

The cboData has the following SQL as Row Source:
SELECT DISTINCT Ordini.IDOrdine, Ordini.DataOrdine FROM Ordini ORDER BY [DataOrdine];

The combo box above will return all records since IDordine is probably unique. I would drop the IDOrdine so that the row source of cboData is as follows:

SELECT DISTINCT Ordini.DataOrdine FROM Ordini ORDER BY [DataOrdine];

You will also need to delimit the date with # signs in the following code (required for all dates)

       .RowSource = "SELECT [IDOrdine] " & _
                    "FROM Ordini " & _
                    "WHERE [DataOrdine]=[COLOR="Red"]#[/COLOR]" & Me!cboData [COLOR="red"]& "#"[/COLOR]

 

[MikeLeBen]

Based on this:

The cboData has the following SQL as Row Source:
SELECT DISTINCT Ordini.IDOrdine, Ordini.DataOrdine FROM Ordini ORDER BY [DataOrdine];

The combo box above will return all records since IDordine is probably unique. I would drop the IDOrdine so that the row source of cboData is as follows:

SELECT DISTINCT Ordini.DataOrdine FROM Ordini ORDER BY [DataOrdine];

You will also need to delimit the date with # signs in the following code (required for all dates)

       .RowSource = "SELECT [IDOrdine] " & _
                    "FROM Ordini " & _
                    "WHERE [DataOrdine]=[COLOR=Red]#[/COLOR]" & Me!cboData [COLOR=red]& "#"[/COLOR]

That fixed it, but there's a problem with date formats, in my country we use the format dd/mm/yyyy, and access seems to go fine by this format with the date pickers in the forms. When running this query, though, I think due to how SQL is designed, it swaps month with day and messes everything up.

 

[jzwp22]

SQL always assumes dates are in the mm/dd/yyyy format. You may want to check out this discussion on work arounds.

 

[MikeLeBen]

beautiful, thanks for your help and the precious link.

 

[jzwp22]

You're welcome