Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am having some issues in access using the DSum function to create a running total in access. I am trying to create a running total for both my Production standard and total conforming parts by using the date as the criteria. I am also doing this in a totals query which is set to pull data between [start date] and [end date]. The formula is working correctly for my production standard creating a running total between the specified dates but the total conforming formula (which is the exact same formula) is summing all of the data and then creating a running total. The are the two formulas I am using:

Production Standard - Running Total: DSum("[Production Standard]","M151-3 by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-3 by Month (with standards)","Date <= #" & [Date] & "#")


I should also note that I used these exact formulas for a different query (sorting by a different shift) and they worked perfectly.


Production Standard - Running Total: DSum("[Production Standard]","M151-1A by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-1A by Month (with standards)","Date <= #" & [Date] & "#")


Can anyone tell me what I am possibly doing wrong and why theses equations work for some of my data and not the others? This is all pulled from the same source I am just creating different reports sorted by shift.
0
Hi,

I have been trying to work with the below code. For Norway it changes to summertime 26th March 2017 and wintertime 29th October 2017. I want the query I am running to display if the current date is summertime/wintertime so I know if I need to add 1hr or 2hrs to the GMT time in my database. It does do this, but not for the dates that I want.

Public Function IsDST(ByVal d0 As Date) As Boolean
   IsDST = d0 >= NextSun("Mar 21 " & Year(d0)) And d0 < NextSun("Nov 01 " & Year(d0))
End Function

Private Function NextSun(d1 As Date) As Date
   NextSun = d1 + 7 - Weekday(d1)
End Function


My Query text: Season: IIf(IsDST([Date])=-1;"Summer";"Winter")

Any help would be appreciated as I would like to avoid having to change the dates manually for each year. I could also go for a table with start date & end date if that is easier.

Bgrds
1
Hello my expert friends.  I need a favor from someone who is excellent at relational Access Databases.  As I mentioned before I am self taught with what I know so far.  I am trying to create a relational database and for some reason when I think I got the tables linked correctly one or another is not communicating with each other.   No matter how much I seem to read up on relational database for some reason I am not understanding something.  Oh also this file is in Access 2000 file format.  I would appreciate any help someone could be.  Thanks so much in advance.  
Also feel free to add data if you need to this is just a practice database I am trying to learn from
Disney-Correctional-Center.mdb
0
Dear Experts,

I have an large Access 2010 report which houses the results from hundreds of surveys.  In the report
the results are all written up in text boxes that can shrink or grow dependent upon the size of the response. Each response to the survey questions is typically 3-4 pages of A4 and is output into individual PDFs.

This all works well but on some pages of the PDF the text box gets cut off as its reached the end of the page.  Is there a way to basically set each text box to "move onto the next page if it does fit" - something akin to dynamic page breaks? or something like section breaks which, if the section doesn't fit then it goes to the next page

Any help or suggestions would be really appreciated
0
Hello All,
I have a interesting problem.
we have one computer on the network that has a local printer.
when i send a report to the printer it print two pages. (second page is blank)
i already checked and changed the margins. it dosnt do it on the other computers.

When i do a preview before printing, it's only printing one page even i send it to the printer from the preview.
Thanks in advance
0
I want to calculate for grade letter using the peace points
0
I want to lookup a table from a subform where the main form has extended detail over the lookup table.

In essence I want to reverse the wildcard in the following:

Me.Cost = DLookup("Attendance Fee", "ContractedSites", "Site Like '*" & Forms![Job Reports]![Site] & "*'")

For example:

the Site on the Form is "Wolverhampton University Main Campus" and the Site in the lookup Table is "Wolverhampton University".

The Table will always have the extended detail over the Form.

Hope this makes sense?
0
I need to pull reports in access based on ID and Name, I wrote a vba function but its NOT WORKING. PLease help, also I need help getting it into the actual access report, it creates a module after it debugs then I get a window that says write another code which confuses me. I am a beginner in this language and have a deadline coming up soon. Please help.

Public Sub cmdcreatereportforeachrecordandexport()
Dim MyFileName As String
Dim whr As String
Dim query As String
Dim scriteria As String
Dim GetEmployeeID As String
Dim GetEmployeeName As String
Dim rptrr As Variant
Dim db As Database
Dim rst As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("sheet1query")
rs.MoveFirst
MyFileName = "sheet1query"
whr = whr & "WHERE (((sheet1query))"
reptarr = Array("ID,Name")
'run the query and create a record set
Do While .EOF

Set rst = db.OpenRecordset("select [ID],[Name])FROM [sheet1]")
'Resets Query to show ALL records
Loop
Do Until rst.EOF
'save:
DoCmd.OutputTo acOutputReport, "performanceappraisal", acFormatPDF, Format("IDName.pdf")
DoCmd.Close acReport, "performanceappraisal"
rs.MoveNext
Loop
MsgBox "scribe files have completed successfully"

End Sub

Open in new window

0
Hi,

I have the following two tables:
(1) tbl_add_product_records
(2) tbl_vehicle_details

Within tbl_add_product_records I have a field "field_vehicle_temp_code" which has a many-to-many relationship with tbl_vehicle_details' field "field_vehicle_temp_code".

Field_vehicle_temp_code in tbl_add_product_records is set as a number and the range of numbers is 1, 2, 3.
1 is inputted where "field_min_product_temp" is <10
2 is inputted where "field_min_product_temp" is <25
3 is inputted where "field_vehicle_min_op_temp" is >=25.

In the tbl_vehicle_details, there are fields "field_vehicle_registration_number", "field_vehicle_min_op_temp", "field_vehicle_max_op_temp", "field_vehicle_temp_code" and a primary key "field_vehicle_ID".

Field_vehicle_temp_code in tbl_vehicle_details is set as numeric and the range of numbers is 1, 2, 3.
1 is inputted where "field_vehicle_min_op_temp" is <10.
2 is inputted where "field_vehicle_min_op_temp" is <25.
3 is inputted where "field_vehicle_min_op_temp" is >=25

What I want in the search form is for a record searched to display , amongst other fields, the records' "field_vehicle_registration_number"(s) where the field_vehicle_temp_code in tbl_add_product_records matches that of tbl_vehicle_details. It should return a minimum of one vehicle registration number or a list of vehicle registration numbers.

An example of how this should work is:
For product A, its minimum product temperature is 24C. There are three vehicles …
0
Hello,

I have a barcode scanning app that has a problem with some aggregates being "used" twice.

There are some nested queries that look like this:

Query to sum up the quantities of barcodes pulled/scanned-

SELECT tblFixedAssets.Staff_ID
	,Count(tblFixedAssets.Staff_ID) AS CountOfStaff_ID
FROM tblPulls
INNER JOIN tblFixedAssets ON tblPulls.ItemBarCode = tblFixedAssets.fldBarCode
WHERE (
		((tblPulls.Job_Num) = [forms] ! [frmI-O] ! [JobToPull])
		AND ((tblPulls.PulledStatus) = 'O')
		)
GROUP BY tblFixedAssets.Staff_ID;

Open in new window


Then a query with the items on the job with the scan results-

SELECT tblJobsLineItems.Job_Num
	,tblJobsLineItems.[Item Number]
	,First(tblJobsLineItems.Category) AS fldCategory
	,First(tblJobsLineItems.Item_Description) AS fldItem_Description
	,Sum(tblJobsLineItems.Quantity) AS fldQuantity
	,Count(tblJobsLineItems.[Item Number]) AS ROWCOUNT
	,First(tblJobsLineItems.fldKit) AS FirstOffldKit
	,Sum(Nz([CountOfStaff_ID])) AS fldPulled
	,tblJobsLineItems.ParentID
FROM tblJobsLineItems
LEFT JOIN [qryI-O-PulledByJob] ON tblJobsLineItems.[Item Number] = [qryI-O-PulledByJob].Staff_ID
WHERE (((tblJobsLineItems.Job_Num) = [forms] ! [frmI-O] ! [JobToPull]))
GROUP BY tblJobsLineItems.Job_Num
	,tblJobsLineItems.[Item Number]
	,tblJobsLineItems.ParentID
ORDER BY tblJobsLineItems.[Item Number]
	,First(tblJobsLineItems.Category);

Open in new window


See the results of these two below.

image of query results
So, the question becomes, how can I structure this query to display the correct "items pulled/scanned"?

The posted queries are access sql, but the backend is ms sql, so fixes in either syntax could work.

Thank you
0
Hi,

I have a form with a frame called "frameproducttype" with 3 radio buttons, "radiomedicaldevice", "radiodrug", "radiorawmaterial".

I have some vba codes attached to the radio buttons. For selecting radio button "radiomedicaldevice" makes visible a text box "txtmdaregno" with certain input masks for the user to key in registration number. Likewise, selecting radio button "radiodrug"makes visible a text box "txtmalregno" with certain input masks scripted for the user to key in a registration number. The radio button "radiorawmaterial" has no registration number so no text box appears.

End result:
In the tables where this record eventually gets saved
(1) I would like the txtmalregno, txtmdaregno to be saved in one field (note that txtmalregno and txtmdaregno have different input masks and one product can either be a medicaldevice or a drug or a raw material but cannot be both).
(2) I would like that upon selecting radiorawmaterial, it is saved as a foreign character in the same field and table as txtmalregno and txtmdaregno so that I can build a query to reference this foreign character to display on a separate search form as "raw material". On that search form it needs to be able to display user data entry with respect to txtmalregno and txtmdaregno but to reference to a separate table and return "raw material" if the foreign character is located.

I am currently using the following code to save fields in
Set rs = 

Open in new window

0
Having sorted out the progress meter, is there an event under a form which can be used to run or call my progress meter at splash form level? Below is the arrangement of my splash form
(1)      The start form opens the splash form
(2)      The splash form takes about 1 minute ( here it uses a timer) before it opens the switchboard and finally closes itself

Now I want to use my progress meter or run it while waiting for the splash form to close, is there an event in access forms I can use to call my splash form and archive my requirement????????

Regards

Chris.
0
I have a large list of client companies whose names must be protected. Each company is associated with a unique numeric code. I need to be able to enter a single code and obtain the company name connected with it, without allowing the entire list to be accessible for viewing or copying -- even if the user had access to the back end database file.

That is, I might know that code 1357 is for company Acme Superior Industries, Inc., but I don't want to type in that long name, but rather just the four-digit code. Acme's long name will then populate a text box on my form, confirming that I used the right code, but I will only ever see the names of companies for which I have valid codes. The important thing is that the user have no way to obtain the full table of names. The front end will be MS Access, and the back end, with the company table, could be Access or SQL Server. Any ideas? Thanks.
0
I have a simple access 2010 app (devopped on win 7) that I want to distribute to users who don't have Access.

I used the package wizard to make a package including my mde together with access 2010 runtime.

Firts, when I tried to implement my package on a Win 8 PC, I got an error message saying it cannot use my VBA code.
I posted a question on experts-exchange whose answer helped me solve the problem.(see https://www.experts-exchange.com/questions/29046888/problem-with-access-2010-runtime-in-solution-package.html?anchor=a42283299¬ificationFollowed=197063312#a42283299)

So, to get it working on Win 8, I used the Solution  wizard to set up the following reg keys.
Root = CU
Key = Software\Microsoft\Office\14.0\Access\Security\Trusted Locations\Ma-Validation
Name = Path
Value = C:\_MA

Now I tried to implement the same package on an XP PC and I get the same error. What should I do differently to implement my app on XP?

Thanks
0
Hi Guys,
I am busy preparing another report (Letter) with Microsoft access.
With my previous reports had the following difficulties:
1) I was unable to find a way of controlling line spacing in a report text box when using rich text format.
2) I have a problem with numbering. When writing numbered sentences that flow on to more than one line the text does not line up.
I have again looked on Google with no luck.
Is there anyone that could assist me.
Thanks a lot.
Regards
Ralph
0
Hi
I would like to find out if there are any proper VBA code that can be used to set the progress meter on a report, I really need this especially on the balance sheet because users might think since the report is taking longer than anticipation then it is not working. Kindly if any elaborate where to put the code.

Regards

Chris
0
Hello, so I have a giant access report that I split into page by page and it came from a table in excel. I am trying to save each file by a)ID and Employee Name (Last, First). I am using vba in access 2016, so I am not proficient in writing code or anything but I have pieced together something below, I don't have a query or anything, all I have are the table and access reports. Its performance appraisals so id really appreciate if someone could fix my code or rewrite parts of it for me. I'm at the end of my rope so I have no idea how to get this thing to work. Help! :(  

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef

On Error GoTo Errproc
Set db = CurrentDb()
stDocName = "Performance Appraisals"
'get path
strpath = DLookup("Performance Appraisals", "ID", "Employee Name", "RecID=1")
If strpath & "ID" = "Employee Name" Then
MsgBox "Please open form and add document path.", vbOKOnly
Exit Sub
End If
Set qd = db.QueryDefs!qgetEmployeeNames
Set rs = qd.OpenRecordset
If Me.txtjob & "Id" Then
Do Until rs.EOF = True
Me.textemployeename = rs!employeename
GoSub Save
rs.MoveNext
Loop
Else
rs.Close
DocName = strpath & rs!job & "IDEmployeeName" & .pdf
Return
Select Case Err.Number
Case 53 'file not found
Resume Next
Case Else
Resume Next
End Select
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

0
I have an older accde file and a form needs one modification (a price) I do not have the accdb
Any way I can modifiy this or am I attached to another object by an inclined plane, wrap helically...

Thanks,
0
This very well could be and/or end up in the gigs section, but i need to collect some more information - education.  

I have an access database that, put simply,  tracks real estate comparable sales (data/pictures) , ties it together with a subject property,  and then outputs a report....

I wish to recreate this database so that it will be able to be access by multiple people in two - possibly more locations.  I've been reading up on Google Cloud - various flavors of SQL and think it can be done, but it I am more willing to pay someone to do this rather than learn something new...  I do however need to understand why choosing solution is A is better than solution B.
0
Hi all,,

I have recently made a successful split of my DB and it is working fine with _fe and _be as intended.

At the beginning of a loading macro (with punch of Queries inside it),  I set the 'setwarnings' to YES but no system message pops as if it set to No.
My DB is Trusted.  The Queries of the Macro are executing as required.

Any help please.
0
Hello All,

 I'm relatively new to Access and inherited a database with a frontend, backend, and two accde versions. The original database was created in Access 2010. Since then there's been a company wide Microsoft Update, that has added Access 2016. I've made change to the databases using the latest version, Access 2016 and had to split the database into new accde versions. We are running a daily update of the database through the Task Scheduler using a batch file and macro. The update is failing and I'm getting a error, 'This error is usually associated with versioning differences between two or more installed versions of Access'. The message continues on, but I'm wondering what I need to do to fix the issue and have the update run properly when using the Task Scheduler.

 Thanks, Bill
0
QueryProductSummary                                    
                                    
Date      ProductID      ProductName      Price      Qty      SalesValue      Cumulative
01/01/2017      201              Tea Biscuits              £2.50        20             £50.00           £50.00
02/01/2017      201              Tea Biscuits              £2.50        15             £37.50           £87.50
03/01/2017      201              Tea Biscuits              £0.95       120      £114.00         £201.50
04/01/2017      201              Tea Biscuits              £0.85         16              £13.60           £215.10
01/01/2017      202              Milk Packets              £0.15        200      £30.00           £30.00
02/01/2017      202              Milk Packets              £0.16        150      £24.00           £54.00
03/01/2017      202              Milk Packets              £0.14        160      £22.40           £76.40
04/01/2017      202              Milk Packets              £0.12        185      £22.20           £98.60
01/01/2017      203              Rice Packets              £1.10          20      £22.00           £22.00
02/01/2017      203              Rice Packets              £1.05          12      £12.60           £34.60
03/01/2017      203              Rice Packets              £1.12          60      £67.20           £101.80

Above is an expected query from the following tables in Ms Access:
(1)      TblProducts
(2)      TBLPrice
(3)      TblCustomer
Now the issue here is to get the cumulative sales value by products like  above , that is where I’m failing to archive , any idea here??

Regards

Chris
0
I need to edit a query which need to locate some infomation in other group by query.

I can't edit the field when I join the group by query.
I try to use dlookup, but the query runs very slow.

Please help and advise

Many thanks
0
Hi
I have this VBA Code , it works very well and update the record in table properly  see below:
If IsNull(Me.CboEmpployee) Then
MsgBox “ Please select Employee to update”
Me.CboEmployee.setFocus
Exit Sub
End If
If IsNull(Me.CboStatus) Then
MsgBox “ Please select Status  to apply”
Me.CboStatus.setFocus
Exit Sub
End If
DoCmd Open “ Qryupdateemployees”
DoCmd. Close
End Sub

The reason why I’m using DoCmd.Close is  to refresh both combos , that isCboEmployee & CboStatus. Now is there some other ways to refresh the combos rather than close.
0
Hi,

I am no vba guru. I have a problem with this code.

Tables
tbl_Manufacturer_Name

Fields in table
Manufacturer_Name
Date_Entered
User_Name

Form
Manufacturer_Name_Form

Fields in the form
txtusername
txtmanufacturer name


In short, what I am trying to achieve is:
1) If txtmanufacturer name is empty then a message box appears
2) If txtmanufacturer name is duplicated then a message box appears and prevents data from being entered. If txtmanufacturername is not duplicated then update fields txtmanufacturername, txtusername into tbl_Manufacturer_Name in the respective fields on one record

I have 1) sorted out. But for 2) whenever there is a new record that is not a duplicate manufacturer name, two records get saved. One record has the manufacturer name (without the username), and the second record has the manufacturer name and the username as what I need.

I think it has something to do with the DLookup function returning a null and somehow recording that entry, however, I have set Cancel = True.

Sorry I should add that, the first entry I make results in the table being updated as a blank entry too

Private Sub btsave_Click()
Dim TempLoginID As String
Dim rst As DAO.Recordset
Dim Answer As Variant

TempLoginID = Me.txtusername.Value
    'reinsert the username into txtusername
Forms![Manufacturer_Name_Form]![txtusername] = TempLoginID

    'txtmanufacturer name is a mandataory field
If IsNull(Me.txtmanufacturername) Then
    MsgBox…
0

Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.