customer db

[Housey]

Hi,

I'm creating a db for customers and each customer can have many service orders.

In the customer form I have two buttons, one to create a new service order and one to open existing service orders.

Both buttons work as they are supposed to although I am doing this with custid, so both the customer and serviceorder tables have a field called custid and when the button is clicked to show existing service orders it matches both custid's and shows the corresponding records. However it onlt works at the moment with me manually entering the custid in the field in the serviceorder table. When a new service order is created I need the custid to be entered in both tables.

I hope this makes some sense, any help is grately appreciated!!

 

[ScottGem]

sorry, but it doesn't make sense. I don't understand what two tables you are talking about. You have a customers table and a serviceorders table. Then you press the new serviceorder button you should already have a customer record displayed. You can then pass this value to the new order. But it sounds like that's what is being done.

 

[Oldsoftboss]

You need to set up a relationship between the 2 tables. Make the customers table the main one on your form and the Service table on a subform. Once this is done, any new orders created in the subform will automatically enter the CustID. This is probably not want you want to end up with, but is an important lesson in how replationships work.

To enter a new order directly into the service orders, create a form with the Service orders as the recordsource. The in the CustID field, change it to a combo box and have its record source as "Select CustID, CustomerName from Customertable"
Set the column count to 2, bound column to 1 and put 0 in the column widths. This will hide the CustID No and display the relevent name.

HTH

Dave

 

[Housey]

Hi,

Thanks for the replies... I had a bit of problem putting my post into words.

There is currently a one-to-many relationship. From customer.custid (primary key field, autonumber) to serviceorders.custid (number) and from the customer form I can bring up the relevant service orders by clicking on a button that has the following code:

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "serviceform"
    
    stLinkCriteria = "[custid]=" & Me![custid]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

However it only works when I manually go into the serviceorder table and enter some numbers in the custid field. This field doesn't get populated when a new service order is created. I don't know if I can use a subform as I also want a button on the switchboard to allow searching via service order number and display the serivce order form with a specific record and therefore the customer form wouldn't be open already so I couldn't pull the custid from there.

Again thanks for your input, I hope this is clearer...

 

[ScottGem]

However it only works when I manually go into the serviceorder table and enter some numbers in the custid field. This field doesn't get populated when a new service order is created.

Ok, there are a couple of ways to do this. I would use the OpenArgs argument of the Openform method (for details on how to use it check Access Help). This would pass the CustID value to the new form. In the OnOpen event of the form I would add code like:

If Me.NewRecord AND NOT IsNull(Me.OpenArgs) Then
Me!txtCustID = Me.OpenArgs
End If

An alternative is to reference the value from the first form. Again using the On Open event, you would use code like:

If Me.NewRecord Then
Me!txtCustID = NZ(Forms!1stFormname!txtCustID,"")
End If

Again the form test if its a new record and if it is references the value in the calling form.

I personally would use the OpenArgs method.

 

[Oldsoftboss]

To enter a new order directly into the service orders, create a form with the Service orders as the recordsource. The in the CustID field, change it to a combo box and have its record source as "Select CustID, CustomerName from Customertable"
Set the column count to 2, bound column to 1 and put 0 in the column widths. This will hide the CustID No and display the relevent name.

Go with my second suggestion. Maybe have all of the fields of Disabled or invisible and use the after update of the combo box to change them. This way ensures there is a Customer in the CustID field.

 

[GabrielAkoh]

Hi,

If you always get to the service order form from customer form, then on the service order form, create an "on current" procedure to
set [serviceorder form].custid = forms![customerform].custid.

This way any time you open the serviceorder form, it gets the current custid.

Good luck!