Replies

That’s waaaaay to simple John! Got anything more complicated?
Thanks a bunch!! I’m outta here! (By the way…) As comp time (the 2 hrs) might vary between accounts, can I add another text box for the comp time?
I tried the following, but got an error.

Billinghours=[balance]*24-[comptime]

Thanks, once again, for the input. I think we both know where I am as far as VBA goes (I haven’t even received the books yet, let alone read any). I did manage to go back through and look at all the relationships and redundant info. I was able to eliminate five tables and improve the lookup funtions simply by removing the redundancies. I’m not sure how the unnecessary repetition affects the db, but the result is noticeably faster.

The only method I can see at this point for getting around the multiple combo boxes would be to use a subform, and I’m not so sure that would be any more efficient from either the operators position or even the application efficiancy. Need to do some more homework.

I have also discovered that there are 53 queries in this application and some (apparently) are not even being used. The same seems to be true of some of the machros. I think, before I go much further, that I should probably attempt to eliminate some of the “garbage” that has been left behind. From what I’ve been told, there have been several different people involved in this db over the years. Also, it was originally created in ’97 and converted to XP.

I do have a copy of ‘Mastering Access ’97” and must admit that just going through this db is a great learning experiance. I can see immediately how important it is to have a game plan before sitting down to create tables and queries particularly. I’m certainly not in a position to be judgemental of anyone else’s work, but I can see how easily one might become entangled in one’s own web if a fundamental plan is not established and adheared to. As I said, this is a great learning experiance.

Thanks again for your assist. I very much appreciate your interest in my developement in this application. Without being to presumptive, I should like to say that I view it as a mentored journey. If so – I certainly look forward to it.

Bryan

Well, let’s see if I’ve got this straight:

There are six combo boxes on the Order form with the same function. Each one of these have a seperate look-up table with common names for shippers or consignees. The only link I can see between these tables is from the seperate look-ups to the Order form table. I don’t really know why.

Just above these combo boxes for the shipper/consignee name is another combo box with only two choises: “shipper” or “consignee”. These are really nothing more than lables as far as I can see. This first one defaults to “shipper” and the other five default to “consignee”. In the real world any of these names selected could be either a shipper or consignee, though obviously at least one of them must be a shipper and one of them must be a consignee. Looking at the need for six combo boxes I have discovered that there may be multiple shipper locations (pickups) as well as multiple consignees (receivers). No one seems to know what would happen if you exceeded six designations on a single order. (Shut down and go home I guess).

Once a name has been selected from the customer name combo box you can dbl-clk this cell to have a form open with the directions, contact, phone number, etc for that customer. That information comes from a seperate table called ‘Directions’. I noted that the directions form may open with more than one record. This is because it searches for the customer by name only. There may be a customer with more than one facility – so it recalls all records for that customer name regardless of the number of facilities. The operator must use the record selection buttons to scroll through to find the correct location (city). There are 2,317 records in the directions table. The only duplications are the customer names. The reason for that is because there are several locations (cities) for the same customer name.

Much of this seems a bit redundant to me, but I’m new at this so maybe there’s a good reason. This isn’t the speediest DB I’ve ever seen. While all the operators want is a way to enter a new customer name without having to go to the underlying table to enter it, I think there is a larger issue here that needs to be addressed. The customer name fields in the Order form table, as well as the Directions form tables (shipper or consignee), have nothing to do with the “customer” who gets billed (there is yet another combo box for the party billed – which may or may not be the same as one of the customer names in the shipper/consignee combo boxes). Much of what is accomplished by the existance of these shipper/consignee boxes is nothing more than screen information and has nothing to do with billing because all of this information is extracted by reports to be manually entered into a seperate accounting program. Talk about inefficient!
My thought is that, although the order entry form is based on its own table, the shipper/consignee combo boxes should have a single source for thier look-up results (that being the Directions table) as the customer name in these boxes is common to all other boxes (i.e. you could have one name for a shipper, but you could also have the same name for up to five consignees with different city locations).
Boy, I bet you’re sorry you offered your help now! I’m still looking at how all the pieces fit together, but I think I will probably be trying to link the Directions table to the Order Form table and re-assigning the combo boxes source to Directions table rather than the seperate L/U tables for each of the boxes. At that point I suppose I could just delete those excess tables after testing. With 2,317 records in the Directions table I sure hope I don’t do an “Oooops”! Anyway, I would like your input on making these changes (if my thinking is right) before even addressing the NotInField issue. What do you think?

Thanks,
Bryan

Thanks for your interest. I should first probably say that I am not the origunal creator of this DB and that I am learning new things about it each time an issue comes up. The combo box in question simply displays a customer name from a look-up table linked to the main form table. However, dbl-clk on this cell and it displays driver directions to this customer from a seperate table. This customer name also appears on some other forms and reports and I’m not quite certain how this all works together, need to do some more research.

My first consideration is: Why would the creator have made a look-up table for the name (there are no other entries in that table) and a seperate table for the directions, which has multiple information cells with names that are common to both tables? Maybe the directions table was an add-in after the fact? I don’t know, but it seems redundant.

Like I said, I think I need to look into the ramifications of any changes to that combo-box before I proceed. I will get back to you once I have a clearer understanding.

I’m really loving this mentoring situation! Really look forward to creative efforts with you!

Bryan

No worries mate! If you’re ever in the states let me know, I’ve got a cool Fosters for ya!

Seriously, every suggestion is welcomed and a valued learning tool. At this point in my venture down Access Madness Lane I appreciate every effort by those who are willing. No apology wanted, requred, or even appropriate.

Bryan

Thanks and a tip of the hat to both Mark and Wendell for the input! Just ordered Beginning Access 97 VBA Programming and 2002 Step by Step. I figured between the two I could determine some basic understanding and let either my satisfaction or frustration guide me to whatever level. I do have to say, despite the frustrations, that this has been a very rewarding experiance. I must say that with all the rubish, and demented freaks, out there in cyber-world; it is most gratifying to find selfless individuals who are willing and able to assist and mentor. I am, indeed, humbled.

Bryan

P.S. While typing this message I was just approached by an operator wanting to know if there was a way to make an entry (as opposed to a selection) in a combo box appear from the drop-down on the next visit. This is NOT another request for help (although it will probably become one). I just have to laugh! We (or rather you) just solved a problem, the keyboard hasn’t even cooled off yet, and here they are asking for more! Is THIS what I have to look forward to in the future?!! LOL

Praise the Lord and pass the tequila – we got a winner!!

This works great! I locked the field in the history and detail views, as well as the new order entry form. Now the order entry form opens without the time, the operator can spend all day staring at the screen if they want, but the time won’t be captured until they have completed the form. As the only other time the form is visible is when they look at the history or detail view (where the cell is locked), the time can never be changed.

I’m going to have to lump you in with the United Way my friend – “I don’t know you, but I love you”. Thank you so much for your help on this and for your advise on the VB self-teach issue. Your selfless response to a desperate amatuer is most worthy of high praise. If ever I should get to the point of actually having some value as a contibutor, I shall not forget my debt.

Gratefully,
Bryan

Thanks for your input Mark. I will look for an updated copy. May I assume that any books on VBA will apply to ANY version? By that I mean to say, will a book concerning VB in Access 97 have the same usefulness in Access 2002?

Thanks Again,
Bryan

I just KNEW I should have been dropping breadcrumbs along the way!! I’m totally lost now.

I actually have three forms originating from the same query (calculated fields) which is based on a single table. They are:

1. Order Entry (which displays a single new order – data entry only – with ‘Order Time’ locked)
2. Order Detail (which displays a single order selected from a dispatch screen (form) – editable – with ‘Order Time’ unlocked)
3. Order History (which displays all orders – editable – with ‘Order Time’ unlocked)

I now realize that ‘Order Time’ should probably be locked on all screens if I can get the time entry issue corrected. As I mentioned in one of my earlier posts, I did have the default set to Now() in the Table, but it entered a time at the moment the form was “opened”, rather than the time the operator began to actually enter an order. Geesh, I think I’m getting dizzy!

Now I’m not sure if I’m supposed to be entering conditions or values in the field, or the form. When you say to use the Before Insert event, do you mean in the field or the form? You say to set the code with no If statement or anything. How to I set a code for this event if I don’t make any entry? It’s 8:48 here – too early to start drinking?

Also, if you would be so kind, please see my other post on VB books – any input would be greatly appreciated!

Bryan (I think)

Thanks Michael but, yes, I do have the correct brackets entered (although that’s just the type of mistake I’m capable of making). Please also see my recent request concerning VB – appreciate any and all input!

Bryan

Well, I’m thinking I just don’t have enough knowledge to deal with this issue, or perhaps even the ability to follow directions. I copied your code to VB and ran it. This is what I got in the ‘Visual Basic Error Message’:

Run-time error ‘2465’:
Can’t find the field “|” referred to in your expression

I went back and looked at the expression and there is no “|” anywhere in it. I’m beginning to feel like Homer Simpson (can’t find the “any” key).

Anyway, I guess I’m back to square one. I do thank you for your help so far and offer my apologies for whatever I’m doing wrong at this point. It must be equally frustrating for you.

Woah! That was pretty scary! I got some kind of screen flash and the application closed.
I re-started and went back and deleted the entry from design view. This is what was entered:

Private Sub Form_BeforeUpdate()
If Me.DataEntry = True Then [Order Time] = Now()
End Sub

I really don’t have any experiance with VB so maybe the above entry has an error in it. Whatever, the app crashed when I ran it. All is back to the way it was before, but of course I still have the issue. Need food – sleep. Maybe a clear head in the morning will help. Very much appreciate your assist – if you have any other ideas (or can show me where I went wrong in entering the code) I’d very much appreciate it.

Thanks for your input Allen – much appreciated. That is the way I had it originally set up, but we found that the operators almost always completed an order and opened a new form. With the default of Now() the time they OPENED the form was entered into the time field, rather than the time they actually took the order. It can be several minutes (even hours at times) before they take another order. This makes the order entry “time” inaccurate. That was my reasoning in using the “On Got Focus” event to trigger the time entry – even though the form was open, an entry would not occur until they started taking the order. Any other thoughts?