Modules & VBA :: Adding Effective Date Indicated By User To All Records
Sep 12, 2013
I'm trying to run a saved import through VBA, add a new column called "Effective_Date", then update this field for all records to the effective date indicated by the user.
It currently works for all records except 1 is always omitted.
Code:
Private Sub cmd_upload_staffing_report_Click()
DoCmd.RunSavedImportExport ("Import-AccessUploadStaffingReport")
Dim strField As String
Dim curDatabase As Object
Dim tblTempEmployees As Object
Dim fldEffectiveDate As Object
[Code] .....
View Replies
ADVERTISEMENT
Sep 19, 2013
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
View 4 Replies
View Related
Dec 23, 2005
I want to put 2 Dbs on a network server. Below are the groups that can get to each directory.
Directory Students: Students, faculty, few staff
Directory Staff: all Staff
I will have a separate workgroup for each Db because each group of users is so different. Each Db will have all deselected user permissions and assigned groups and group permissions.
Questions:
-Could a computer programmer student get into any code in the Db in the Students directory, even tho they would not have user-level permission to do so?
-Could a student somehow affect in any way the other Db in the Staff directory?
In other words, could somebody use the Db in the Students directory to cause trouble anywhere else in our network?
Thanks
View 1 Replies
View Related
Jan 17, 2007
Below is the code I have for a query. This query shows Processes, Operations, etc. Due to multiple Effective_ (Effective_ stands for Date) The Operations show multiple times. What I would like to do is have each Operation show only one time based on the latest Effective_ i.e. If Operation START-UP is listed three times due to three effective dates 2007-01-04, 2007-01-08 & 2007-01-17 I would only like to the the one associated with the 2007-01-17. Is there a way to accomplish this in my query?
SELECT ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
FROM ASSYROP INNER JOIN ASSYWC1 ON ASSYROP.OPERATION = ASSYWC1.OPERATION
GROUP BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
ORDER BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.EFFECTIVE_;
View 3 Replies
View Related
Mar 27, 2015
I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.
For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.
Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.
View 1 Replies
View Related
Feb 10, 2014
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
View 2 Replies
View Related
Jul 24, 2005
Hi,
I was wondering how difficult it would be to do the following:
1. A user opens up a form.
2. The user uses a combo box to select his/her name.
3. There is a date combo box on the form and by default, it is set to today's date. So when the user selects his/her name, the records for today are displayed. Changing the date will show only the records for the date shown.
4. The user will have the ability to add and delete records specific to the criteria chosen in the text boxes. Adding new records will only add records for the date chosen in the combo box. E.g. if he/she adds a record for today, it will only be seen when today's date is selected.
Will a subform have to be used for something like this? Or could it be done with one form using filters?
Any thoughts and/or approaches on this would be greatly appreciated.
TIA.
View 3 Replies
View Related
Sep 9, 2013
I have button the when pressed will insert a record (via an insert into query) to the frmMain and several records to the frmSub.The problem is when a new record is created in the main form the auto number primary key advances. This is fine however the linked Child field which is a foreign key of the main form is not added and the value is blank. I thought this would happen automatically and the Master field would force the child field to be update but this is not happening.Do I have to determine the max number used in the auto number then add 1 to it?
View 7 Replies
View Related
May 26, 2015
I am working with a database that deals with trailers. What is happening is a salesman takes an order for a make and model for a trailer for a customer. The customer can then add some customization to the trailer such as more tail lights or tie down straps. They add the customization on a form called frmCustomQuote. On frmCustomQuote there is a subform called sfQuoteConfigs. On sfQuoteConfigs there is fields such as category and sub catergory that get populated bases on what was selected.
On frmCustomQuote there is a button called "Copy Quote" this will allow the salesman to make an exact copy of the trailer and customization. The quotes are held in a table called tblQuotes and the customization is held in tbQuoteConfigs. Now my problem is when I click on Copy Quote it only copies the first record into tbQuoteConfigs. I can't figure out a way to tell my code to move to the next record within sfQuoteConfigs.
My code:
Code:
Option Compare Database
Option Explicit
'Setup ADODB connection to the tblQuotes
Dim adoQuotesCustomQuote As New ADODB.Recordset
'setup ADODB connection to the tbQuoteConfigs
Dim adoQuoteConfigsCustom As New ADODB.Recordset
'Dim the Variables
[code]....
View 4 Replies
View Related
Feb 17, 2015
The goal I am trying to accomplish using VBA within Access 2010 is to append/add certain records to another database (Customer Service) from my database (Client Request). I tried using a macro to append then update records in my database but there is a "lag" time as to when the information is updated in the 'Customer Service' database causing my updates not to occur.
Here is the SQL from my query:
INSERT INTO Issues ( Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description )
SELECT [TBL003_Combined Data].CUSTOMER, [TBL003_Combined Data].TITLE, [TBL003_Combined Data].[DUE DATE], [TBL003_Combined Data].[OPENED BY], [TBL003_Combined Data].[OPENED DATE], [TBL003_Combined Data].PRIORITY,
[Code] ....
And here is how I "thought" I could fix it through Access VBA:
Function UpdateIssuesDatabase()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
On Error GoTo UpdateIssuesDatabase_OpenError
con.Open _
[Code] .....
I have highlighted where it is giving me the issue.
View 9 Replies
View Related
Jun 3, 2014
I have got a received form that has 2 subforms.
The main form is based on Received Table
The 1st subform is based on a query that shows the order detail and how many units are outstanding.
The 2nd subform is based on a receivedDetail table. so will have a list of items with the original order qty, and the qty still outstanding.
When the main form loads i want it to create new records into the 2nd subform based on the 1st Subform.
so the 1st and 2nd subform run parallel with each other and appear to be as one to the user.
both subforms are continuous style.
Code:
Private Sub Form_Load()
With Me.Form.RecordsetClone
Do While Not .EOF
.AddNew
!UserFK = Forms!frmReceive!cbxUsername
[Code] ....
View 3 Replies
View Related
Nov 13, 2013
I have two tables :
Table 1: Mission_Id , Mission_Type, Customer_Name...
Table 2: Supporter_Name, Report_Date, Area, Unit, Issue_Type, Error_Status,Mission_Id
I have a form that the users enter data into and send a report each day. I would like that in a click of a button all the data I entered the day before and that have ="Open" will be entered into the tables with today's date. Is that possible?
View 4 Replies
View Related
Jun 9, 2014
Is there a way of looping through a form record set, while adding new records to a different form record set? using some data from the 1st record set in the new records?
View 8 Replies
View Related
Mar 4, 2015
I total novice at VBA. I am trying to code a button to modify (the last) record in a subform list and then add a new record based on values in unrelated or unbound fields on the button form.
The following code is based on the first of two YouTube tutorials (this bit on the edit) and looks like it should work. Except that my Access 2010 with Visual Basic for Aplication v7 does not recognise the type definition Database or Recordset
Code:
Private Sub ANOwner_Click()
Dim cn As Integer
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
[Code] .....
View 6 Replies
View Related
Jan 7, 2015
I am trying to clear a textbox after the user enters an invalid date and I do not know why the following code is not working:
Private Sub txtStart_AfterUpdate()
If Not IsNull(Me.txtStart) Then
If Not IsDate(Me.txtStart) Then
MsgBox "You have not entered a valid date"
Me.txtStart = Null
Me.txtStart.SetFocus
End If
End If
End Sub
View 3 Replies
View Related
Jun 28, 2013
I want whenever I'm updating or adding records to my form, the ID automatically take the value of the previous ID and increment it by 1.
The field type is text (mixed with number) - PM0000000.
I've done some research, what I understand is that I need to:
-do a lookup and
-find the MAX of the number portion.
Name of form - Payment
Name of table - Payment
Name of field - payment_id
I tried these, but to no avail...
Private Sub payment_id_Click()
payment_id = DLookup(("[payment_id]", "Payment", "[payment_id]=Forms![Payment]![payment_id]-1")payment_id + 1)
End Sub
[Code] .....
View 8 Replies
View Related
Dec 25, 2013
I am new to access 2010. I have a table called "Forecasts", and I wish to update the timestamp with todays date, whenever the user makes a change to the qty of goods forecasted.
I have been researching online for the solution to the correct format to datetime, but it doesnt seem to work.
Code:
Dim todayDate As Date
todayDate = Date
Dim sqlString As String
sqlString = "UPDATE [Forecasts] " & _
[Code] .....
The messagebox shows:
UPDATE [Forecast] SET Branch_Plant=123, Item_Number_Short='222', Description='AAA', UOM='EA', Estimated_Cost=123, Requesting_Business_Unit='AAA', End_Customer='CCC', Project ='Secret', Timestamp=#26/12/2013# WHERE ID =24
Then I hit a syntax error. Whats wrong with sqlString?
View 6 Replies
View Related
Jun 12, 2006
I am looking to do a calculation that in excel would be a snap, but im looking to do it in the access database for the functionality that comes with it. I have a bunch of Agent codes and with each is associated a certain percentage split between them. So for example: 123 = 40% agent A and 60% agent B. These codes are listed in a dropdown menu so upon selection i would hope the formula would apply. There are maybe 15-20 of these codes, and they range from 1-3 agents. Would it be easiset to do a ton of If statements in an unbound text box? OR would it be easier to make a calculate button with If statements to calculate upon click. A brief exmple either way (or any orther ways that are better yet) would be greatly appreciated.
Once again what im looking for (abridged version):
1. pick the code
2. Input doller amount
3. Have it autocalc (if possible) or just calc on click
Also to be noted: the percentages are being taken of of a manually input percentage depending on other factors. So in essence the fields to be worried about are Initial doller amount, percent of that, and then the percent split off of that number. Thanks in advance!
-Bob
View 5 Replies
View Related
Dec 11, 2006
After almost 10 years developing MS Access applications for the government, I'm attempting to write an effective resume to begin my job search. I've single-handedly designed, tested and implemented over 40 small to medium sized applications to handle everything from classified document management to personnel tracking to event planning. Most of these systems were fairly complex with pages of VBA code, automation and FE/BE structure to allow concurrent users.
I'm having a terrible time verbalizing the work I've done with Access while making it sound impressive to companies using larger, more robust systems like SQL Server or Oracle.
With the stigma that comes with Access, I would love to know how Access resumes could be written to accurately portray the ability to design logical schema, design intelligent interface and define system requirements while conveying that the skill to perform these tasks translates to other systems as well.
Can anyone here (PatHartman, ghudson, et al) provide:
A) a sample resume for an Access developer?
B) sample language to include in an Expert Access Developer resume?
I thank all of you, in advance, for your time and any assistance you can provide.
Happy coding!
View 4 Replies
View Related
Jul 16, 2014
I am trying to set a date filter that filters between two dates (Start/End) after I have selected filters from other combo boxes. This is what I Have so far and is a bit of a mess.
I have two text boxes - txtStartDate and txtEndDate
If Nz(Me.txtStartDate.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False
ElseIf Me.cboCity.ListIndex <> -1 Then
Me.Form.Filter = Me.Form.Filter = "NextCallDate
[Code] .....
So the idea is I will filter all records by my name then by status then by city and then by start date and end date.
View 2 Replies
View Related
Nov 30, 2014
I am trying to create a form that will select records by date range. The form contains 2 textboxes, with pop-up calendars to select dates. I managed to come up with this code, however, it's not working. Whenever I run the code, my database just goes blank, as though it's selecting something that is not in the records.
Code:
Private Sub Command16_Click()
Dim Task As String
Dim startDate As Date
Dim endDate As Date
startDate = Me.Text12
endDate = Me.Text14
Task = "SELECT * FROM Final WHERE Final.Timestamp BETWEEN #" & startDate & "# AND #" & endDate & "#;"
Me.RecordSource = Task
End Sub
How does one actually do the date range search? Could it be because my timestamp actually has both date and time, that's why the date search is not working?
View 5 Replies
View Related
Sep 24, 2014
I have a table which specifies the delivery date
I have a from that allows you to choose a year and a month.
I have an unbound textbox which I wan to display the count
I want to be able to count all the records from a table with the year and month specified in the comboboxes and display this in the texbox.
View 4 Replies
View Related
Sep 30, 2004
Newbie here.
I would like to add some text help describing what the fields in an Access form mean. Under "Help" it mentions a What's This button which can be found in the properties box under design view. I have been unable to find any "What's This" fields in the property box. Can anyone point me to this?
Also, I saw that I could create a rich text file to be displayed when a user presses the F1 key. Can this just be an HTML file? It mentions needing to compile the .rtf file. How does one compile this?
Any insightful comments would be greatly appreciated.
Thank you.
G
View 1 Replies
View Related
Nov 10, 2014
I am trying to export records to excel that are in a given month based on an input month. The user would select the month with the records desired and click the button to export them to excel. The attached zip file has an image of the database and the code behind the button is:
Code:
Private Sub Command33_Click()
DoCmd.OpenReport "HLA_TAT", , , "Len(Exception & '') > 0 AND Receive_Date > #" & Forms!HLA_TAT.Date & "#"
End Sub
[CODE] can not access referenced form HLA_TAT [?CODE]
View 11 Replies
View Related
Jun 6, 2014
Suppose I have a table with a variety of fields; one autonumber, some text fields and some date fields.
I need code to calculate the number of fields that are NOT date fields and then code to count how many of those fields (the ones that are not date fields) have data in them.
Following is the code to count how many fields in a table had data and this works well, but now I need to find a way to ignore the date fields. How can this code be modified to do the same but ignore the date fields?
Code:
Public Function PercentCompleted(Table As String, IDProject As Long, TotalFields As Single) As Double
Dim sampO As DAO.Recordset, iCtr As Long, strSQL As String, Percent As Double
'Takes the fields based on the projectID
strSQL = "SELECT *" & _
"FROM " & Table & " WHERE IDProject = " & IDProject
Set sampO = CurrentDb.OpenRecordset(strSQL)
[code]....
View 13 Replies
View Related
Jan 19, 2015
Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
View 4 Replies
View Related
