Can my form fill in a field, yet allow me to override?

Each customers is sent to us from a salesman, and we provide service for 1-7 
days before the activity ends.  A new charge is generated for each day of 
service. 

Employee #1 uses the customer service form to generate a bill, and includes 
the name of the salesman.  Employee #2 uses the form to generate a bill on 
the next day, and has to look up the first bill, in order to know which 
salesman to record.  The same thing happens every day until the activity ends.

With each new referral for service, there's a strong chance it will be due 
to a different salesman, so the customer number and salesman can't be rigidly 
linked.  But the employees don't like having to go find the most recent bill 
to know which salesman to credit.  Can the form propose a name, based on the 
most recent prior one?
0
Utf
2/19/2008 8:28:02 PM
access.forms 6864 articles. 2 followers. Follow

1 Replies
529 Views

Similar Articles

[PageSpeed] 18

There may very well be a much easier method for this, but one method might be 
to create a function that will find the Employee on the most recent Invoice 
for a particular Customer.

Below is something very quickly compiled on the top of my head, and makes 
certain assumptions regarding table and field names, etc.  You would then 
simply set the DefaultValue of the text box to 
"=GetMostRecentBill([txtCustomerID])".
-------------

Private Function GetMostRecentBill(ByVal lngCustomerID As Long) As String
On Error GoTo Err_Handler

	Dim db as DAO.Database, rs as DAO.Recordset
	Dim stSQL as String, stOutput as String

	stSQL = "SELECT tblEmployees.ID, tblEmployees.EmployeeName 
Max(tblInvoices.InvoiceDate) AS LatestInvoiceDate FROM tblInvoices INNER JOIN 
tblEmployees ON tblInvoices.EmployeeID = tblEmployees.ID GROUP BY 
tblInvoices.CustomerID HAVING (((tblInvoices.CustomerID)=" & lngCustomerID & 
"));"

	Set db = CurrentDb()
	Set rs = db.OpenRecordset(stSQL,,dbReadOnly)

	With rs
	If Not (.bof and .eof) Then
	     stOutput = !EmployeeName
	End If
	End with

	GetMostRecentBill = stOutput

Exit_Handler:
	If Not rs Is Nothing Then
		rs.Close
		Set rs = Nothing
	End If
	If Not db Is Nothing Then
		db.Close
		Set db = Nothing
	End If
	Exit Function

Err_Handler:
	MsgBox Err.Number & " – " & Err.Description
	Resume Exit_Handler
End Function

----------------

Again, there is probably a much simpler method to accomplish this, in which 
case I am certain that the Access gurus that reside here (and who possess 
much more knowledge than I do) will happily offer it to you.

In any case, I hope this helps,
Jeff


"David K" wrote:

> Each customers is sent to us from a salesman, and we provide service for 1-7 
> days before the activity ends.  A new charge is generated for each day of 
> service. 
> 
> Employee #1 uses the customer service form to generate a bill, and includes 
> the name of the salesman.  Employee #2 uses the form to generate a bill on 
> the next day, and has to look up the first bill, in order to know which 
> salesman to record.  The same thing happens every day until the activity ends.
> 
> With each new referral for service, there's a strong chance it will be due 
> to a different salesman, so the customer number and salesman can't be rigidly 
> linked.  But the employees don't like having to go find the most recent bill 
> to know which salesman to credit.  Can the form propose a name, based on the 
> most recent prior one?
0
Utf
2/19/2008 9:14:03 PM
Reply:

Similar Artilces:

Looking for an excel function which can mirror opposite the data
Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB...

can't insert, delete or move pictures
can't insert, delete or move pictures Sheet protected? parker wrote: > > can't insert, delete or move pictures -- Dave Peterson ...

Override font setting
Hi, Can I override font setting when read a received email automatically? Because some font style very hard to read when received and I have to change font style one by one (sometimes cannot change) Thanks! William ...

How to stop RichEdit color change overriding default color
I've got a handly little syntax editor going along (that--so far-- is waaay faster than the examples on codeguru.com (if you'd like to know how to speed them up, I spotted a logic flaw in two of them. If you're interested, just email me). I have a question on a different syntax editor I'm writing for an in-house language though, so you'll notice some unique features that enable me to take shortcuts. But I've got a funny little problem. If the user is typing at the end of the rich edit control, and they type in a keyword and it gets colored, any text typed after tha...

Can I replace word by word in italics?
Is there a search/replace feature in Publisher that allows you to replace a word by the same word in Italics? -- Marv Not in Publisher, but you can in Word using wildcards. Might try copy/paste into Word, do the editing, copy/paste back into Publisher Help here http://www.gmayor.com/replace_using_wildcards.htm -- Mary Sauer http://msauer.mvps.org/ "WISEMANOFNARNIA" <COHENMARVIN@LYCOS.COM> wrote in message news:1f5149a6-f337-4db7-9aed-78132e9c9ae8@j12g2000vbl.googlegroups.com... > Is there a search/replace feature in Publisher that allows you to > replace a word by ...

active form name
Hello!! What function returns active form name? Thanks!! Cesar There is no function to return the current field, window or form name. You can track the specific windows you are interested in using PRE triggers to set a global variable with the name or id or the current window. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions polic...

Pulling data for a report from a form
Hi, I have a product report list that want to show the on hand qty. so i have this formula in a report field: =onHand([pkProductID],Forms!FrmMyDate!InvDateQty) but when i preview the report it shows #name? instead. Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 To refer to a textbox on another (open) form, use: =Forms!FrmMyDate!InvDateQty -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "josros60 via AccessMonster.com" <u56159@uw...

Unable to add custom field to a view/preview
I added a new attribute to the Quote and I am able to add this attribute to the form but the new attribute does not appear in the list of fields when I create a new view. Further, the new field does not appear in the list of fields if I try to add it to the quote preview. I am able to store and retrieve the value of the new field using the API. Thanks for any info. I figured it out. JohnE wrote: > I figured it out. Hi John! How do you solve the problem? Publish and IIS reset don't help. I don't see custom fields in "Customize views", but i can see custom fields ...

Field Service V2.0
I know that there probably isn't a lot of solid info avail about feature set for V2.0 but I have seen a number of references to planned addition of some type of Field Service functionality. My question is.... Does anybody have any insight or thoughts as to whether or not the ability to schedule/dispatch techs and the ability to generate invoicing for field service calls will be included? There is something referred to as Service Scheduling in the latest information that I have seen. "SteveT" <drumguy61-google@yahoo.com> wrote in message news:1102972818.458191.150090@z14...

can not delete emails
i went out of town and returned with 3000+ emails. some i can delete, some i can not. i have tried to repair and fix, i have tried to create a new profile and import/export everything. any ideas? thanks in advance. yes, the cannot move item(s). The initial error i get when trying to delete is: "the message interface has returned an unknown error. if this persists, restart outlook". The only thing i can think of is a damaged profile??? >-----Original Message----- >Do you get an error when trying to move the item to another folder (e.g. >move the item to the deleted ...

Can I retrieve deleted email from the enlarged database
Version: v.X Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Email Client: pop My v.X database is 950Mb with very little email. I know this is because of the deleted emails of the past and that rebuilding the database would produce a new database the right size. I would like to know if I can get to the deleted emails. Can I get them back. I have run in to an issue that I need them. <FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:12pt'>On 5/11/10 11:41 PM, in article <a href="59bb859f.-1@webcrossing.JaKIaxP2ac0&q...

How can I line up 2 columns with similar information?
I have 2 columns with names in each and I'd like to line them up next to each other. I think we'll need more information, Kathy. I don't understand what you're trying to do. Can you give exactly what's up? ************ Anne Troy www.OfficeArticles.com "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:A5C0BC96-7E27-4C55-89A3-03513FC0D409@microsoft.com... >I have 2 columns with names in each and I'd like to line them up next to >each > other. Column A has employee number, Column B has the same employee numbers but has addi...

right align int fields
I would like to right align integer fields. I would like to do this in a while/for loop in onLoad event, because I have many fields. How can I see in javascript if a field (I do not mean the value of a field) is an integer. Marc ...

GST Tax Forms for New Zealand
Hi there, I have recently prchased Money 2005 Standard, ans the box has big claims about it being able to track GST on purchases etc. That all seems to be working well, except I cannot get the GST information out of it at all. How do I setup my tax forms to give me the totals that I need for my GST return? Thanks in advance Jerome Brown Dear Jerome, I also would like to display the GST in a final report. Did you have any luck elsewhere? Marcus "Jerome Brown" wrote: > Hi there, > > I have recently prchased Money 2005 Standard, ans the box has big claims > about...

Script to auto fill Creditor doc # in Auto Invoice
Hi. We are setting up RMS HQ + 42 stores to integrate into GP using Connect Pro which is going great so far. Connect Pro integrates purchases into Shipment only and we then have to Auto Invoice all receipts by supplier to process the roughly 5,500 invoices at store level per month. We can run a macro to populate the creditor doc # at auto invoice stage, but is there any other utility / sql script / vb script available which we can use to bring the PO Title across from RMS tables (RMS po # = Batch ID) without having to manually process 5,500 invoices or use the macro? This would t...

Redefine field data type
Hi, I am trying to write a function in a module in MS Access 2000 that will change the data type of a field called 'Start' in table 'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I run it nothing happens...no errors or changes to the table. The code finds the table and field, creates a new field called 'temp' then copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The field 'Start' has data type dbDouble. Any help would be great!! Public Function ChangeFieldType() 'Purpose: Changes a f...

Can MsPubliser do INCLUDETEXT or MasterDocument/SubDocument equiv?
I'm preparing a series of bifold + duplexed booklets. There are 8 page, 16 page, 24 page, and 40 page versions with much duplicated text. I've used Word's Master-Document with sub-documents (and also the INCLUDETEXT fieldcode) so that changes / typos only have to be fixed in one file. I'd like to use the same MasterDocument approach with Microsoft Publisher 2003. Is this capability available with Publisher? This would be for duplexed quad-fold versions that end up being 4.25"x5.5". Does Publisher 2003 have the feature of a master document being able to include separ...

Publisher calendar, how can I show the previous/next month?
I need to show the past and previous months in blank days at the top of a per month calendar, but can't see how to do that. Thanks. Create a yearly calendar in a new publication. Ungroup, copy the separate months, paste to your main calendar. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jennie" <Jennie@discussions.microsoft.com> wrote in message news:AF4CF698-ABB8-4F01-A667-CC6AA87A9732@microsoft.com... >I need to show the past and previous months in blank days at the top of a per > month calendar, but...

Can I run ON from an external drive without it being installed on
Can I run ON from an external drive without it being installed on a local machine? "Ang130" <Ang130@discussions.microsoft.com> wrote in message news:5982603B-D3AC-48F3-84A4-D96DB4696889@microsoft.com... > Can I run ON from an external drive without it being installed on a local > machine? With Ceedo software Ang130 wrote: > Can I run ON from an external drive without it being installed on a > local machine? YES, if run under Ceedo with the Argo installer.[1] In theory it should also work with a USB stick. It would have a superfast stick, m...

How do I create a "floating" header in Excel so I can always see i
contact me at andy@boydrealestateinc.com Andy To have row 1 always in view as you scroll down. Select A2 and Window>Freeze Panes. Rule is: whatever is above and left of the selected cell will be frozen after you hit Window>Freeze Panes. i.e. select B2 to have Column A and Row 1 frozen. Gord Dibben Excel MVP On Wed, 16 Nov 2005 13:38:00 -0800, Andy Heal <Andy Heal@discussions.microsoft.com> wrote: >contact me at andy@boydrealestateinc.com ...

Changing Report Fields
What happens to the reports if we change screen fields. Does anyoneknow what happens in the event of: - adding a field – presumably doesn’t appear on any report - changing a field – label or properties - removing a field? Any help please? Hi, - Adding a field would change nothing to a report. You will have to alter the report itself to make it visible there. - Labels are set inside the report itself, so this won't change if you change them on the form. - Removing the field will not change the report, but it will be empty becaus the user cannot enter data for that field (unless som...

Cell can left indent; anything for right side of cell?
Is there a way to leave more space between the end of a word and the right side of the column? I indented the left side of the column by using the indent option set to "1" in the alignment tab of the formatting box. Besides adding an extra column to the end of the row and making it very small in width and extending the border to that extra "spacer" column, is there a way to make this space? My spreadsheet is done and if I have to do this, I'll have tons of work re-writing code. I was going to use Word for the final presentation of the data, but it didn't work ou...

Want to see only specific number of records on a form
Hi I have a form with default view as "Continuous Forms". When I openthis form I only want to see the records equal to Recordcount i.e. ifI have 3 records I should only see three records. I dont want to seean extra one record which is blank. The easy way is simply to set the allow additions to "no".(you will still be able to add records via a code, or perhaps a "add" button you provide.Or, perhaps you don't even need add record ability...Anyway...give the above a try..it will remove that extra "new record" entry....-- Albert D. Kallal (Access MVP)...

IMS que is filling with tons of e-mail in Exchange 5.5
Hi When ever I tried to select the Hosts and Clients that successfully authenticate option in routing restrictions. I see tons of mail que in IMS. Actually I tried to check for 2010 event and I see some junk user account which says "\temp" which is not at all in my domain or in local users. Can anyone tell is it some virus or some one is creating the account test remotely to spam. Please advis ...

Can't Email to Moved Domain
We had a sister company operating within our WAN. We hosted their mail on our single Exch 2003 server. This company was sold and moved off of our servers. We removed all of their information from our Exchange server. I have deleted all of their mailboxes, AD user accounts, any reference to their domain in the recipient policies, and any entries in our dns system for their domain name. Still, when we try to send messages to these people at their new location we get an NDR that says "The e-mail account does not exist at the organization this message was sent to. Check the e-mail addr...