The General Customer - Service issue

[Blabolat]

So I have posted here before about an issue and got a work around, however there was some ambiguities and I'd like to get a real solution.

I have a client table.
I have a client product table for ski's
Each Client has 1 or more products (skis)

I have a Service Order table and form that I use a drop down control in the Service order to select the customer.

In the Service Order Form a I have a continuous Subform for detailed service.

This is where I'm having the problem.
In the detail subform I want to select from a drop down box the customers particular ski that I want to service.

I can't figure out how to have only that one customers ski's to show up.

I've asked before, and was presented with a solution, basically a filter, however, though it works, I can actually insert anybodies ski ID number and since it's not bound to anything, there won't be an error, but it will be wrong.

I can't imagine this is a unique thing and if anyone has any thoughts on this, I'd appreciate any suggestions.

Cheers!

 

[Blabolat]

I should say, combo box, not drop down box. sorry.

 

[Isskint]

Create a query that shows all SKIS and the associated Client. Put a criteria on the Client ID = to the forms ClientID. Something along the lines of:

SELECT ClientProducts.ClientID, ClientProducts.SKI FROM ClientProducts WHERE (ClientProducts.ClientID = Forms!ServiceOrderForm.ClientIDComboBox)

You would then set this query as the RowSource of the combobox in the detail subform.

 

[Blabolat]

Create a query that shows all SKIS and the associated Client. Put a criteria on the Client ID = to the forms ClientID. Something along the lines of:

SELECT ClientProducts.ClientID, ClientProducts.SKI FROM ClientProducts WHERE (ClientProducts.ClientID = Forms!ServiceOrderForm.ClientIDComboBox)

You would then set this query as the RowSource of the combobox in the detail subform.

I appreciate the help and I'm sure you are on to something there, but
I JUST CAN"T DOit.

Actually it's tennis racquets, I"m making a Stringing database, that keeps track of players racquets when they bring them in to string. (I was somewhat embarrassed at it since, no one has done this as far as I know). I created one before that I still use, but thought I'd refine it somewhat.

so what I have is
Tables:
Players with [ID]

Racquets with: [ID], and [Players_ID] that I associate with the Players

Service_Order with [ID], [PlayerID] and [FrameID]

Service_Detail(that will be a continuous form of detail service for each Service
Order) with [PlayerID][FrameID][SOID](associates with the Service order table)

SO a player will come in to get his racquet re-strung. I go to the Service Order, look up his name.. (which is fine)
But here, is where I"m not sure how to go about, I can select his racquet from the Service order(which I can't even do right now) and do service for that racquet. However, sometimes they bring in multiple racquets. Previously, I select the racquets from the Service Detail Subform and do all racquets on one service Order.

I'm thinking maybe it would be better to do one Service ORder per racquet. I"m not sure, this way, I select the racquet once.. and just do the service detail, the other way, I have to select the racquet in each Service detail Record.

Either way, I'm really having trouble with having the option of multiple racquets per person.

I just want a Combo box that will show only that players racquets and give an error if it's the wrong Racquet ID

Like I said before, someone was nice to show a workaround by using a cascading combo box, but, that method still allows me to enter willy-nilly values without error.

So if anyone is bored and has the patience to help, will be more than grateful.

Thanks K

 

[Isskint]

I just want a Combo box that will show only that players racquets and give an error if it's the wrong Racquet ID

And that is what i gave you. That query will list all the rackets for the specified client, so the comb box will only show those.

Cascading combo boxes are not a 'workaround'. That is a standard practice and will equally provide you with a solution.

 

[Blabolat]

Thank you Do appreciate the help.

 

[Blabolat]

I can't get that to work..

 

[Isskint]

can you attach your DB or some screen shots of the relationships and/or table structures?

 

[Blabolat]

Thanks.. I think I can, I"m uploading it, it's not very big right now.

I created this before, but it was so bloated with .. well junk. that I decided to redo it, and I have been using pieces from the other database so the forms still have links to the old database that I"m redoing..

I appreciate, you taking some time on this, it's work in progress.

 

[Blabolat]

this file is a little better.. at the very least the links work.

Also..
While messing around with it, I discovered that their is a little switch in the properties menu, where it says to "limit to list".

That worked.

Thats what I was looking for. So thanks!

 

[Blabolat]

On the other hand..

Each customers racquet is uniquely identified with a label and associated to the player, usually a bar code that I can scan in. quickly.

What I do in the Service Order, is enter the Players number.. and then enter the racquet number.
What I would like to do is just put in the racquet number, there by cutting out half the steps.

I'm thinking in my simple ways that, since the raquets are associated to the player, there should be some way of via query or something that it can pull out the players number and automatically fill in the players ID number.

Is that a feasible thing to do?

Look I really do appreciate the help, I sit here, not being a programmer and bang my head quite a few times so.. Thanks to you all who do help and it is really nice to see how generous everyone is in their time, really. thank you.

 

[Blabolat]

Yet another dumb question.

When I'm in my Service order and I select the customer, I do it by 1 of 3 ways, either type in their last name through a combo box, 2) Typing in the Client ID number(usually scanned) or 3) use the Frame ID to get the Client ID(also scanned)

Thats 3 boxes, I was wondering if it's feasible to create 1 box and have it look for the Client ID, Last name, or the frame ID.

Can you use a OR statement in a query?

 

[Blabolat]

Never mind,
THe player number and the Racquet # are the same format, so, it would be hard to differentiate them apart unless, I went to another ID scheme.

 

[Isskint]

What I would like to do is just put in the racquet number, there by cutting out half the steps.

There are several ways to do this. The simplest, i would suggest, is to use a combobox based on Q_Player_racquets. Then use the After_Update event to assign the PlayerID. The SQL for the combobox would be along the lines of;

SELECT Q_Player_racquets.Racquets.ID, Q_Player_racquets.Racquet, Q_Player_racquets.Players.ID, [First_Name] & " " & [Last_Name] AS Name FROM Q_Player_racquets ORDER BY Q_Player_racquets.[Racquet];

So when a user select a raquet they will see the raquet name, the combobox value will hold the raquetID and the after update event would be;

Private Sub cmbRaquet_AfterUpdate()
    Me.PlayerID = Me.cmbRaquet.Column(2)
End Sub

Can you use a OR statement in a query?

Yes you could, it would be an Iif() function and you would need to use Dcount() also, but it would be messy and like you just posted PlayerID and FrameID's can be the same. It is something i have used before, it makes for a cleaner interface and simplifies the users life.

 

[Blabolat]

Wow, thank you, That is exactly what I want to do.
I think I did come across a solution very similar to yours and this is what I did
(I had to change the Form names and some of the other labels as it was getting to confusing for me how I originally created it, so I simplified it, but the references are the same.

SELECT [Frames].[playerid], [Frames].[FRAMEID], Frames.[Frame] FROM Frames;

I don't really care about looking up the Frame ID, because I print a small label with a bar code which I attach to the racquet, and I scan that into the field.

I am curious, why you put this in the afterUpdate()

Private Sub cmbRaquet_AfterUpdate() Me.PlayerID = Me.cmbRaquet.Column(2) End Sub

In the combo box, I assigned the [PlayerID] to be the bound field.

Never the less it's coming along, I"m pretty happy about it despite it's newbie look.

 

[Blabolat]

OK its been one head banger after another, and it has been a head scratcher with every issue, but with google and help from everyone here, its getting done.


I have a silly issue that is driving me crazy, because I don't know how to google it.

I have the form and sub form.
Very little if any data entry into the main form so I set my focus directly into the subform

However, there is a date field, that is defaulted to Today + 3. The problem is I have to go back and I don't ever now how to evoke it, just comes up with the correct date.

If I don't do anything the field is Null, nothing and is recorded that way and thats the issue.

So I'm wondering what can be done to automatically trigger that date event.

 

[Blabolat]

Never mind, found the solution.. I'm going to thank myself.

On another note.
one of the hardest things for me to do was learn syntax of Forms,fields,commands in VBA..

So my solution was to, from where I was, go into the "expression builder", and go down the hierarchy of the Forms, records and fields, then just cut and paste it into the VBA script.

Seems to work pretty good, keeps it formatted correctly, but I don't know, if there is a short cut or not.

 

[Frothingslosh]

If you're talking about referring to a control on a form from code associated with that form (say, automatically doing something when a form opens, or perhaps running code when a button is pressed), you can refer to the form itself with the reference 'Me'. That means a control would be 'Me.ControlName', and a control's property would be 'Me.ControlName.PropertyName'.

This page may help: http://access.mvps.org/access/forms/frm0031.htm

 

[Blabolat]

Thank you, those are great references.