I have a list of account numbers for members, they have distinct member numbers. I have it set up for users to search for the member, then the member number will auto-populate. I also need to account for members with no member number. I would like to have the user click on the check box "Non-Member" and somehow assign a Member number...starting with NM. So the first non-member would be NM00001, next NM00002. I thought I could create a table with the sequence of NM numbers. I am just unsure of how to tell my form to populate that field when the Nonmember box is checked.
I am trying to make an Update Query that will update a table that has the same account numbers and assign them a value....ie.1,2,3,4 and so on. Does anyone know how I can do that?
Example: Accout Num Assigned Value 12345 1 12345 1 12345 1 12544 2 12544 2 12568 3 12569 4
During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)
I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.
During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)
I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.
I have a table called tblAccounts and a form called frmEnter_new_accounts. In my frmEnter_New_Accounts form is a Text Box for Account #. I want to have it so that when the user enters an account # it checks the tblAccounts table for a duplicate account # and then displays a message box (or Pop-Up form) to tell the user that the Account Number entered already exists. I would also like that box to offer the user the option to either Close that form without saving or return to the form to reenter a different account #.
I know I would probably do this in the LostFocus event or BeforeUpdate.
Does anyone have any ideas or samle code to assist me in this adventure?
On the opening of a form, I'd like to have an input box appear that asks the user for the account number but I'm stuck on the code to make this happen.
Table 1: Account Number Start Date End Date Cost data** Budget data**
Table 2 (Imported excel file with cost/budget data): Account Number Cost data** Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.
I have one table named Entry. This table have account number,work organization, price, date and etc.
I use a combo boxes in form which is connected to query and I can easily choose one account number,one work organization and date to see an report. There is also a possibility to left some of the fields blank, it will bring report if is null.
Now I need to make a form that have a possibility to bring up a report for multiple selections of account number and work organization?
I need code that I can incorporate with the code below, that will notify a user when required fields are left blank so that they have to go back and fill it in before updating the record. Below are the objects (shown in the order they appear on the form):
If any of the objects above are empty, the user should be prompted to go back and fill them in setting the focus back to the first empty object (again the fields above are in order). If conditions are not met, do not run the code below. If the conditions are met then proceed with the code below.
Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMsg As String Dim iResponse As Integer
' Specify the message to display. strMsg = "Do you wish to save the changes?" & Chr(10) strMsg = strMsg & "Click Yes to Save or No to Discard changes."
I have a database I am using to record financial transactions. I have a transaction edit/entry form that uses combo box lists to select the different segments of my 32 digit account numbers. The issue I am running into is that when I enter a value value in the first box/field the form jumps to the very first record.I then can use the navigation buttons to get back to the last record, and all the subsequent boxes/fields work fine without jumping to a different record.
I am using a template database that I downloaded from the Microsoft Access template website and have been modifying it and adding new forms and tables etc. I have used the forms wizard to create a number of new forms to edit a number of the tables. The forms are "split forms".I can't seem to get many of the forms to stay the size that I set them to! They seem to have a mind of their own and often when I think that I have sized them correctly, I then open the form and they display in full screen mode or larger!
I am working on a form and need it to be able to generate a random number to use as an invoice number. The only parameters i need it to meet is that it is at least 5 digits in length. I was hoping i could use a button to make the number generate in a separate box.
I currently have this code which tells the user there is a an accout number already on the db:
Private Sub AccountNo_AfterUpdate() Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("Select * From Spreadsheet where AccountNo = '" & Me.AccountNo & "';") If rs.RecordCount > 0 Then MsgBox "This Account Number Is Currently Within The Database And Will Not Be Allowed.", vbInformation, "DD Cancellations" Me.AccountNo = Null End If End Sub
I want a peice of code which instead allocates a letter to the duplicate account, so user enters a duplicate account, the account will then turn into 123456789A, upto 5 duplicates are allowed, so upto 123456789E. Once "E" has been reached accountnum box will turn null with msgbox. I tried out Case statements but that doesn't seem to work.
A client wants to e-mail newsletters using a non-default Outlook account. The code below does everything the client needs except setting the SendUsingAccount. In debug I can see that the correct account is assigned, yet all of my testing results in e-mails where the From line is the default account. The test setup uses my own isolated SMTP server, so when I look at the e-mails sent the sender is the default account. The default account's Sent folder shows the sent mail, which is not what we want.
Code: Sub prepEmail() Dim frm As Form, startDate As Date, endDate As Date Dim rs As DAO.Recordset, strSQL As String, intNewsLetter As Integer Dim rsEmail As DAO.Recordset, rsNewsletters As DAO.Recordset Dim OlApp As Object, ol As Object Dim olMail As Object, olAcct, olAcctTemp
It seems I've overstepped in setting up Users and Permissions, in MS Access 2007, on an .mdb. When I enter any of the user names and passwords I've created, I'm told that it's not a valid account name or password. I'm wondering how this could be possible since I'm staring at them in the Snapshot file.
My question is this: I can access the .mdw file as "Admin", is there anyway to unwind this mess?
I have a several templates in outlook 2003 for my charity work.
All is OK if I send the odd one now and again, but the workload is increasing with more and more at any one time. However having seen various posts on here, I was thinking I could set something similar in Access as well as keeping track of the payments & deposits more easily.
I am fine with to, cc,bcc, body, subject etc, but what I need to do is SendUsingAccount, but 2003 does not have this property, it started with 2007.
As well as sending on that particular account I also need the signature linked to that account.
I could add that signature by code if I can get the correct account and for some reason the signature is not added.
I tried using SendOnBehalfOfName which works as far as the correct email address is used, but still uses my default a/c.
As an aside, my Outlook session would always be open, so I will use GetObject and not CreateObject method, but would like not to have the warning dialogue in Outlook pop up all the time.
I'm designing a query that must calculate a future date based on a recorded date. Unfortunately it's not as simple as just using adddate (unless I was using Excel!). Here's my requirements:
Future Date = [StartDate] + 45 business days
I am able to take weekends into account, but haven't been able to figure out how to bring holidays into the equation. I have an existing holidays table that I am using for another query within the database (calculates # of business days between 2 existing dates).
Hope this makes sense... I've been staring at it all day so I may not be thinking clearly anymore.
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
What works: This gives me the most recent account, but I cannot get the amount ========================= SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO FROM CBALANCEMIB GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances) ========================== SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT FROM CBALANCEMIB GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
What works: This gives me the most recent account, but I cannot get the amount ========================= SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO FROM CBALANCEMIB GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances) ========================== SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT FROM CBALANCEMIB GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
I have a form that has a bunch of check boxes on it. The user selects specific check boxes My code then has a few If then statements to test which ones were selected. With each one that is selected a query is run to select specific records in a table. (querying out the values from an email field) The user then hits a button to send the email. Microsoft Outlook opens and the emails list is added to the "To" in the email.
Everything works great...But I have a slight issue. If a user has permissions on more than one account when they go to send an email they have the ability to choose a "From" (thus specifying which account to send from)
The user can currently select which one they want but I want this to be more automatic.
CAN i specify which account to send from automatically when the email opens. BUT Just for this application...
Does that Make any sense?
THanks in Advance
Code:Private Sub Command23_Click()' This section is Calling seperate Subs that verify if a Radio Button is selected' If selected it builds the string needed for the email.Call TestCheck3Call TestCheck5Call TestCheck7Call TestCheck9Call TestCheck11Call TestCheck13Call TestCheck15Call TestCheck17Call TestCheck19Call TestCheck21Call TestCheck24Call TestCheck26Call TestCheck39Call TestCheck41Call TestCheck43' This part concatenates the email string being build andDim FinalEmailString As StringFinalEmailString = ""FinalEmailString = Test03 + Test05 + Test07 + Test09 + Test11 + Test13 + Test15 + Test17 + Test19 + Test21 + Test24 + Test26 + Test39 + Test41 + Test43' THIS SECTION TESTS THE CONCANTENATED STRING TO SEE IF IT IS NULL' IT THEN GIVE THE USER THE CHOICE TO CONTINUE WITHOUT ADDING EMAIL' ADDRESSES OR TO STOP AND MAKE A SELECTION.If FinalEmailString = "" Then Answer = MsgBox("You must first select a Group to email, if you wish to continue without adding an email list just hit No", vbQuestion + vbYesNo, "???") If Answer = vbYes Then Exit Sub Else ' This part sends the string to the email and opens the email DoCmd.SendObject acSendNoObject, , , , , FinalEmailString, "Alert Group", , True End IfElse ' This part sends the string to the email and opens the email DoCmd.SendObject acSendNoObject, , , , , FinalEmailString, "Alert Group", , TrueEnd IfEnd Sub' EXAMPLE OF ONE OF THE QUERIES BASED ON THE CHECK BOXESPublic Sub TestCheck3()Test03 = ""If Check3 = True Then Dim dbs As Database Dim rs As DAO.Recordset Set dbs = CurrentDb Set rs = dbs.OpenRecordset("Select * From tbl_Business_Name WHERE TYPE = 'APARTMENTS'", dbOpenDynaset) Dim strTO3 As String strTO3 = "" Do Until rs.EOF strTO3 = rs!EMAIL & ";" rs.MoveNext Test03 = Test03 + strTO3 Loop rs.Close 'Close what you opened. Set rs = Nothing Set dbs = NothingEnd IfEnd Sub
