Hello,
I have a form to add data. I have a cmdbutton to output a shapshot of a report via e-mail. I have noticed that the shapshot does not capture the data I have just entered on the active form unless I don't first close the form.
Is there a way I can capture these active records?
Thanks.
What is the best way to capture the users that modify the records in the database?
Let us start from User Log-in Form After log-in, Main Form will open The main form has various menu (example Add Customer) Once I open the "Add Customer Form", I want to record the username in the Createdby/Editedby Field automatically.
I've always used an audit trail for tracking changes of records that already exist but I have had a situation come up where I now need to track records that have been deleted. For example, if I delete a record in the datasheet view.
I've seen several examples how to do this, with the most common seeming to store the data into a temporary table while waiting for the delete confirmation. If I am reading the notes correctly, it's done this way to capture occurrences when multiple records are selected to be deleted. Is this the best approach for this type of task?
I am working on a project where I need to monitor employees who are on vacation. I would like to run a report that would show employees who are currently on vacation, and those that have already returned. This is the information that I am looking to get from the report.
Search Dates: Entered criteria thru qry, msg windows asks for "start date" and "end date".
1/01/05 - 1/31/05
tblName
John Smith
tblEmployee#
1234
tblDate left (on Vacation)
12/25/04
tblReturn Date
1/25/05
Days on Vacation
31
tblName
Tina Roberts
tblEmployee#
1243
tblDate left (on Vacation)
1/25/05
tblReturn Date (If the employee has not returned it would give the "end date" and calculate the days on vacation)
1/31/05
Days on Vacation
6
This may seem simple but I just can't get the qry to show dates beyond the "start date" and "end date". Only those records that start within the two dates.
Any help wpuld be greatly appreciated.
Hello, I have a problem with my database and need some help.
My database has a main menu named MENU which allows to access all the different forms of the program I am trying to develop. One of these forms is named MASTER and has its source to the main table of my db also named MASTER. To open the form and add a new recorset I simple press a button to which I have associate the following simple code:
DoCmd.OpenForm ("Master")
DoCmd.GoToRecord , , acNewRec
Once in the form and add the data I have to close it so that it can be saved. I then go back to the MENU form and use a listbox to open the selected record. The listbox its based on a query of table MASTER and has a criteria (SSN) that allows me to open the record with the double click event.
My problem is that I need to append my data into different tables and do not know a method which would allow me to do this without having first to close the active form (MASTER) and then running the append queries. Is there a way this can be done?
What I am trying to do is:
- Write my data into the active form
- Append my data from the active form into different other tables without having to close the form
- Still have my active form opened in my database so that I can open another form (CHILD).
Please note that after that I have closed the form, I am currently using this code to append the Master Table to the other tables:
If Not IsNull(DLookup("[SSN]", "child", "[SSN] = '" & Me!SSN & "'")) Then
Forms("MENU").Visible = False
Else
If IsNull(DLookup("[SSN]", "child", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("AppendChild")
End If
End If
DoCmd.OpenForm ("Child")
This avoids having to save twice the same data.
Can anyone help me on this?
Thanks.
Hello,
I am using a form to add data and would like to know if is possible to run a code via macro that allows me to print the records that are showing on the screen (I believe called the active records) without have first closed and saved the data.
Thank you.
I have a query from a table (to reduce the number of fields).The list is all the positions people have performed, as person could have one record another could have 10 the number is unlimited.
Fields are:-
-employee ID
-start date
-position
I need to find any records that were active ON or AFTER 01/09/2014. This will be 1 record for most but some could have multiple.I think it makes it more difficult that there is no END DATE.
I have a main form that controls 2 subforms. sbfActiveProjects displays a list of records that have not been flagged as complete. Upon clicking on a record in sbfActiveProjects, the details of that record show up in sbfProject. This functionality works great, except when I try to create a new record. The code works fine for getting a new record, however once I run the command I lose my ability to navigate to other active records.
Code:
Private Sub cmdNewProject_Click()
'set focus to sbfProject
sbfProject.SetFocus
'navigate to new record
DoCmd.GoToRecord , "", acNewRec
End Sub
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is:
SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header
FROM tblSomething
WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
I have set up a standard form using asp code, the data currently get emailed to the address i requested, i also need this data to go in a database. any tips.
thanks in advance.
JR
Does anyone know what VBA code I use in Access 97 to capture the mdb file name of the database I am currently in? I need to enter into a field on a table.
Any help most appreciated.
Regards,
Dalien51
i was told that i didnt need to store the string value day.. eg.. monday.. tuesday.. because access can retreive the day based on the date..so...i have a textbox on my form that displays the date as follows..23/12/2006i am basically creating a html email and at the moment i use the following.. Option Compare DatabasePrivate Sub Command254_Click()'References: Oulook LibraryDim strEmail, strSubject As String, strBody As StringDim objOutlook As Outlook.ApplicationDim objEmail As Outlook.MailItemSet objOutlook = CreateObject("Outlook.application")Set objEmail = objOutlook.CreateItem(olMailItem)strEmail = Me.txtbookeremailstrBody = "<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01//EN' 'http://www.w3.org/TR/html4/strict.dtd'><html><head>" & _"<meta http-equiv='Content-Language' content='en-gb'><meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'></head><body>" & _"<p>" & Me.txtjobdate & "</p></body></html>"strSubject = "London Heathrow Cars - Booking Confirmation"With objEmail .To = strEmail .Subject = strSubject .HTMLBody = strBody '.Send 'Will cause warning message .DisplayEnd WithSet objEmail = NothingEnd Subthe email displays the date as it stands in the textbox..is there a standard method in access i can use to convert..23/12/2006 to Saturday 23rd December 2006??
View 12 Replies View RelatedHi:
This is something very simple, but I am messing it up terribely. If someone could please help me out with this.
I have a form which has a combo box. This combo box contains a list of Query names. What I am trying to do is simpely, when you highlight on any of the query names and click the run query button it should fire the query the result should be displayed in the datasheet view. I am not able to get a hang on how to do this.
It would be really helpful if someone could help me out with this as i need to get this done ASAP.
Thanks
MS ACCESS 2000 SQL
I need to find out which emails in our database have our domain emails mispelt incorrectly.
For example, our domain email should be miguelangel.duo@leoburnett.es but it may be mispelt miguelangel.duo@loeburnett.es. How can I run an SQL that return the
FirstName, LastName Email from the Extract080907 table with mispelt email domain names?
Is this possible?
I need to create or adapt from a similar database a means to capture time (labor) spent on projects. I've searched for this topic, but have come up empty. Perhaps I'm just not searching on the correct search terminology. Here are the basic requirements:
One department can have many employees, but each employee is a member of only one department. An employee can be transferred from one department to another, and records created when the employee was a member of department 1 need to be retained under that department, while records created after the employee has been transferred to department 2 need to be recorded as such.
Projects can be worked on by any employee in any department. Many employees can work on any project.
Employees need to log in to the database daily and report the time spent on each project. A list of all projects needs to be displayed with the ability to enter the number of hours spent on each.
Reporting to include grouping of employees into departments; time spent by a department in total on each project; who worked on which project and total time spent.
There is a management shakeup here and I'm under a lot of pressure to get this done. I've done a very simple database, but nothing as complex as this.
Any and all help will be GREATLY appreciated.
Hi,
I am nearing the endgame of a FE/BE app. The FE will sit on various PC's across a LAN. IS there a way that when a user opens a record (via a form) on his FE (on his PC), the IP addy of that PC will be automatically entered into a hidden field on the form and store it in a purpose specific field on the BE table.
So I guess my question is how do I get the IP into a field on a table? Either "default" in the table, or via a field on a form? And what would the syntax for such an auto-populate be?
Thanks
Brian
Pretoria
South Africa
I have a stored query in my db. The query gets the recordcount of all the items in a table.
In my VBA, rather than code a new db conection, set a recordset etc, I want to execute the query using DoCmd
=======
DoCmd.OpenQuery stDocName, acNormal, acEdit
=======
The above line displays the results of the query.
What I really want is to run the query and store the recordCount in a variable - without displaying the results.
Can this be done?
Thanks all
Not sure whether this really belongs in the Forms, or VBA or forum, picked Forms because I think it stems from that, despite probably involving VBA in the solution...
I'm getting used to using filter by form and find it quite useful for those off-the-cuff queries, however, it is possible to 'capture' the records so that the results of the filtering can be passed to a readymade report (so that they can be printed out in a more presentable manner than the table view)?
(To what would the Record Source of the report be set?)
Tim
:) Hi Guys,
I have a small problem, hope you can help me out. I have created a a form in Access for data entry. But I would like the form to capture the data and time and the user who's entering the data into the form.
How do I do that. Been trying to figure it out:rolleyes: . Hope you can help. Attached is the screenshot of the form I created. Thanks a bunch
http://img21.imageshack.us/img21/5612/screenshot4ic.th.jpg (http://img21.imageshack.us/my.php?image=screenshot4ic.jpg)
http://img21.imageshack.us/my.php?image=screenshot4ic.jpg
Hello everyone,
Simply put, I want to create a form to which I can enter a raw SQL statement (such as: SELECT * FROM contacts without having to create a new Access query.
The way i have tried to do this is by creating a form which uses an onclick function to pass the string variable (the query statement) to a sencond form and have the second form run the statement. I can pass the variable ok but I cant figure out how to the use the variable string in the second form to execute as the "Record Source" of the form.
Any ideas?
Thanks,
Curveo
I am developing a Student Database that shall capture Biometric and Pictures. I got the fingerprint on an access form. However I cannot get image directly from the webcam to an access form. I have to use the manufacturer's software to capture the image before importing to access using .picture property.
Is there any way of capturing the image directly from the cam to an access form?
I have a query which exports to a excel file afterwards, however, it's not capturing all the data I need.My query is at the bottom, and I think the problem is the "(([tbl Master].ID)=[tblRequest].[Cost Centre]))" statement, as this means that it'll never pick up a Cost Centre starting with "Z", as they don't exist in [tbl Master] (they're exceptions to the rule basically).
What I need to do is only parse the statement "(([tbl Master].ID)=[tblRequest].[Cost Centre]))" if the Cost Centre doesn't start with "Z", if it does, then it'll find it in the table, as there's validation on the point of entry that don't start with it.
Code:
SELECT tblRequest.RequestID, 'N/A' AS [Week No], tblRequest.[Refund Date] AS [Date],
tblRequest.Requester, tblRequest.Authoriser, tblRequest.Refunder AS Processor,
tblRequest.[CRIS Reference] AS [CRIS Ref No], tblRequest.[Customer Title] & ' ' & [Customer Name] AS [Customer's Name],
[code]....
Is it possible to capture a form's filter? In Access 2013, I have a form (from a template database) listing contact information. I can set filters by clicking on the column headers and selecting which records I want displayed. There is also a button to run a mail merge with Word. I want the merge to only include those records displayed on the form at the time it's run.
View 1 Replies View RelatedLet's say I have three columns; apples, berries and bananas. I need to capture, either using the GUI or the SQL if any of those are not null, then bring that row back. So the rows that come back from the query are only ones where one of those three columns is not null.
In excel it would be if(or(apples<>"",berries<>"",bananas<>""),X,Y)
I have a form where there are many users to enter or update data. I need to capture the username who created a record , then , on form load , I want system to check for username if it is same as the creator of a record , then allow update , otherwise don’t allow update , but allow only adding new records. How can I do this
View 3 Replies View RelatedIs there a way of creating a log to capture changes in a table by users?
In a current database that I'm taking over, there was a lot of records deleted from the main table and now I need to figure out what happened.
So, if I would be able to have a log that records all the actions taken by users, I could at least see who did what.