Advertise Here

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Microsoft Access

217K

Solutions

209

Content

51K

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.

Sub Report Reference

Dear Experts

Here is again another issue , my invoice uses a sub report for line details , now the problem is to get the subtotal in the parent customer invoice invoice report , I have to reference the control total in the sub report report footer to my parent customer invoice invoice report footer as well , quite Okay it works well but the total of all details is showing in the parent invoice detail as well. I do not want this ,currently I have just turned that control invisible so that its looks somehow smart. Is there a way to do it properly without hiding the sub report control?

Regards

Chris

Microsoft Access

2 Comments

I set some fields in the table to have 2 decimal places, yet every time I enter data, there are 4 decimal places. What am I doing wrong?

Microsoft Access

36 Comments

I am in need of some assistance. I have created several select queries and made a form. In the form I put in a Autonumber ID such as 2752 and click a button. This button is supposed to run in VBA and Delete * from 4 different queries.

Private Sub btnStep1_Click()

On Error GoTo ErrorHandler


If Me!txtSKU.Value & "" = "" Then
  MsgBox "Please pick a SKU!", vbCritical, "Surpius Says!"
   Exit Sub
Else
 'Do Nothing
End If


DoCmd.SetWarnings False
 DoCmd.RunSQL "DELETE * FROM qryDeleteProdlocations"
  DoCmd.RunSQL "DELETE * FROM qryDeleteAssembliesChild"
   DoCmd.RunSQL "DELETE * FROM QryDeleteProducts"
    DoCmd.RunSQL "DELETE * FROM QryDeleteSKU"
     DoCmd.SetWarnings True
        
Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Open in new window

In all the Queries I can manually delete all these records perfectly.

3 out of the 4 queries have more than 1 table and will error in VBA
qryDeleteProdlocations
qryDeleteAssembliesChild
QryDeleteProducts

DoCmd.RunSQL "DELETE * FROM QryDeleteSKU" Runs perfectly as QryDeleteSKU is only 1 table.

The error says It can't delete from the specified tables. But I can manually delete them all fine.

Here is the most complicated query that involves 3 tables called qryDeleteProdlocations

SELECT SKUs.SkuID, Products.ProductID, ProdLocations.LocID, ProdLocations.QtyLoc
FROM (SKUs INNER JOIN Products ON SKUs.SkuID = Products.SkuID) INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
WHERE (((SKUs.SkuID)=[Forms]![frmDeleteSKUs]![txtSKU].[Value]) AND ((ProdLocations.QtyLoc)<1));

Open in new window

4 Comments

Question by

smithmrk

22h agoSolved

VB.net & MS Access 2013 - Insert Data

Hello Experts!

So I'm working on converting some programs over from Inserting data into FoxPro to now inserting that same data into MS Access 2013...because lets face it FoxPro is gone.

Now comes my issue, in FoxPro I had a field set to character length of 4...in MS Access same field text set to length of 4.
However there are times I get data in the file that is longer than 4 and when importing that data into FoxPro it would just truncate the data from say 6 to 4 and insert it...however in MS Access it doesn't strip it from 6 to 4 it throws an error.

"The field is too small to accept the amount of data you attempted to add."

Is there a way to set MS Access (property of some kind) that will just truncate (ignore the length) the data if it is longer than 4?
Or do I need to change my program to pull all fields from left to right and truncate all the fields in my program?

Thanks,
Mark

3 Comments

I have a form with a textbox where UPC codes are entered and I have some VBA code that validates what is entered, and pops up a message when symbols are entered, if the value is more than 12 characters or less than 12 characters. All works well except if i enter a UPC greater than 12 characters or one where symbols are entered. Because I if the user selects not to continue or has to correct the Symbol entry the control is cleared and now there is less than 12 characters and that condition fires. I want to tell the user what they have entered in those conditions so that they can correct them if needed.

Private Sub UPC_ACTUAL_BeforeUpdate(Cancel As Integer)
Dim MResponse As Integer
Dim LResponse As Integer
'This call a Function and returns True if the value is numeric, False if it is null or not numeric.
If PatsIsNumeric(Me.UPC_ACTUAL) = False Then
    MsgBox "The UPC Number is not numeric. Please correct it.", vbOKOnly
    Cancel = True
     Me.Undo
     End If
    
 'Else
 'This checks if the entered UPC is greater than 12 characters
  If Len(UPC_ACTUAL.Text) > 12 Then
MResponse = MsgBox("This UPC is GREATER than 12 Digits... do you want to continue?" & vbCrLf, vbYesNo)
If MResponse = vbNo Then
   Cancel = True
     Me.Undo
End If
End If

'Else
 'This checks if the entered UPC is less than 12 characters
  If Len(UPC_ACTUAL.Text) < 12 Then
LResponse = MsgBox("This UPC is LESS than 12 Digits... do you want to continue?" & vbCrLf, vbYesNo)
If LResponse = vbNo Then

Open in new window

…

6 Comments

I have multi users
I would like to create a chart when the user logged in and logged out daily basis on MS Access.
Which chart should I use?

Microsoft Access

4 Comments

Question by

apsonline

1d ago

Combine two SQL queries in Access

I have an Access database written to keep track of events. I am trying to combine the following two queries and have the results added together. Both of these work independently, just trying to find the easiest way to combine their numeric results. In the second example, the "Spouse" field is blank. Any help is appreciated.

SELECT Count([Invitee])+Sum(Abs(IsNull([Spouse])=False)) AS RSVPYESCount
FROM Invitations
WHERE (((Invitations.EventID)=[Forms]![Events]![Invitations_Subform]![EventID]) AND ((Invitations.RSVP)="Yes"));

SELECT Count([Invitee]) AS RSVPSpouseOnly
FROM Invitations
WHERE (((Invitations.EventID)=Forms!Events!Invitations_Subform!EventID) And ((Invitations.RSVP)="Spouse Only"));

5 Comments

Free Tool: Path Explorer

LVL 10

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I am creating an inventory database to keep track of hosts in the company building. If a host is scrapped, I can tick the tick box in the 'Scrapped?' field for the host in question and it will appear in the scrapped hosts query. How would I go about automatically populating the field 'Date Scrapped' with today's date, so for future reference I don't need to input the date myself.

Thanks in advance if you can help

4 Comments

Hi Experts!

Is there a way I can have a proper invoice number on my invoice report , my invoice report is generated from the following tables :

(1) tblInvoice
(2) tblLinedatails

Currently I'm using PK number to act as an invoice number from tblinvoice, what I want is something like "Year & 000000 & PK number ) example something like ( 2017 000001) assuming the 1 represent the tblinvoice PK

Regards

Chris

5 Comments

Question by

yasanthax

1d ago

SSIS and TLS 1.2 compliance

Hi

I have inherited an old database which uses a MS Access Frontend to Import data into a SQL Backend via SSIS package .
It appears the connections to SSIS in the following Access DB Vb script fails as it only complies with TLS1.0 and not TLS1.2 as desired when using web based access.
Code:
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=MySQLData;Initial Catalog= MyProject;User ID=MySSISImport; Password= XXXXX;"
cnn.Open

To resolve this to be TLS1.2 compliant should I be looking for
1- A security patch for SSIS
2- Re-write connection string an alternative way

Thanks

3 Comments

Is your access database is frequently showing “MS Access Has Detected That This Database Is In An Inconsistent State” error?

Microsoft Access

0 Comments

Add Comment

Question by

Benjamin Helfman

1d agoSolved

MSACCESS VBA menus greyed out

My VBA editor menus are grayed out. The menus don't respond to mouse clicks or Alt key combinations. I have tried uninstalling and reinstalling MS Office twice and no change.

5 Comments

I had this question after viewing Show details of selected record at top of the datasheet.

How can I accomplish this https://www.screencast.com/t/HZNWrukfn ?

3 Comments

Access 2010

SELECT dbo_t_nsc_trackcode_trans_time_entry.NSC_ID_Ref, dbo_t_nsc_trackcode_trans_time_entry.opened_at, dbo_t_nsc_trackcode_trans_time_entry.inprogress_or_closed_at, DateDiff("h",[OPENED_AT],[inprogress_or_closed_at]) AS Hours, DateDiff("n",[OPENED_AT],[inprogress_or_closed_at]) AS Minutes, ElapsedTime([OPENED_AT],[inprogress_or_closed_at]) AS [Duration Elapsed], TotalTime([OPENED_AT],[inprogress_or_closed_at]) AS [Duration Total]
FROM dbo_t_nsc_trackcode_trans_time_entry
WHERE (((dbo_t_nsc_trackcode_trans_time_entry.opened_at)>=#4/1/2017#))
ORDER BY dbo_t_nsc_trackcode_trans_time_entry.opened_at;

Open in new window

What I need:
I need to run another query based on this query above to give me
distinct dbo_t_nsc_trackcode_trans_time_entry.NSC_ID_Ref
and
total this field: TotalTime([OPENED_AT],[inprogress_or_closed_at]) AS [Duration Total] , (hours minutes and seconds)

Thanks
fordriaders

Public Function ElapsedTime(Start As Date, Finish As Date) As String
'Calculates elapsed time between 2 date/times and
'parses it out into Hours-Minutes-Seconds in HH:MM:SS format
Dim HoursLapsed, SecondsLeft, MinutesLapsed, SecondsLapsed As Long

TotalSeconds = DateDiff("s", Start, Finish)

HoursLapsed = Int(TotalSeconds / 3600)

SecondsLeft = TotalSeconds Mod 3600

MinutesLapsed = Int(SecondsLeft / 60)

SecondsLapsed = SecondsLeft Mod 60

ElapsedTime = Format(HoursLapsed, "00") & ":" & Format(MinutesLapsed, "00") & ":" & Format(SecondsLapsed, "00")


End Function

Public Function TotalTime(Start As Date, Finish As Date) As String
'Calculates elapsed time between 2 date/times and
'parses it out into Hours-Minutes-Seconds in HH:MMS format
Dim HoursLapsed, SecondsLeft, MinutesLapsed, SecondsLapsed As Long

TotalSeconds = DateDiff("s", Start, Finish)

HoursLapsed = Int(TotalSeconds / 3600)

SecondsLeft = TotalSeconds Mod 3600

MinutesLapsed = Int(SecondsLeft / 60)

SecondsLapsed = SecondsLeft Mod 60

TotalTime = Format(HoursLapsed, "00") & ":" & Format(MinutesLapsed, "00") & ":" & Format(SecondsLapsed, "00")


End Function

Open in new window

Microsoft Access

6 Comments

I had this question after viewing Make this access database run in protected mode from a file share.

How do I show the details of this selected record at the top of the datasheet?

https://www.screencast.com/t/GiMJscb5V

1 Comment

All this database has thus far is a table but also needs an entry form that is unlikely to corrupt the database if opened and edited by several users at once from a file share.
IDCom.accdb

5 Comments

Hire Technology Freelancers with Gigs

LVL 10

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question by

correlate

1d ago

Access anonymise fields

Dear experts

I need to find a way of anonymising the contents of all fields in a table (table-master) to remove peoples names

I can produce a list of names in a separate table (ToAnon) and I need a piece of code that will find a name listed in ToAnon and replace it in table-master with '(deleted)' wherever it occurs

Can anyone help, it's an urgent request

Microsoft Access

6 Comments

Question by

yoducati

2d ago

SQL for Cascading Combo Box

I am posting this question as a follow up to the question at this link:

https://www.experts-exchange.com/questions/29057032/ComboBox-on-Access-Form-with-Blank-Selection-Option-to-Clear-Filter.html#a42300096

I have a form with two unbound combo boxes, one for the course and one for the preferred session of that course. Each are located in the header of a continuous form which displays each request submission that has not been assigned to a session. The user is able to filter the list based upon both the class and the preferred session.

I need to add the cascading functionality so that if the user filters the list by course only the preferred sessions for the requests for that particular course show up in the preferred sessions filter combo.

Here is the SQL I have for each combo box:

Course

SELECT qryReqPendingAssignment.Course, tblCourses.CourseName FROM tblCourses INNER JOIN qryReqPendingAssignment ON tblCourses.CourseID = qryReqPendingAssignment.Course UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblCourses.CourseName;

Preferred Session

SELECT qryReqPendingAssignment.PreferredSession, tblEvents.EventDesc FROM tblEvents INNER JOIN qryReqPendingAssignment ON tblEvents.EventID = qryReqPendingAssignment.PreferredSession UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblEvents.EventDesc;

7 Comments

Question by

Andy Connock

2d agoSolved

Microsoft ODBC for Oracle

setting up a new Windows PC. have already installed the Oracle client and the application that uses that seems to work fine. however, we need ODBC access to the Oracle data as well and on an older PC i'm able to Add New Data Source and see "Microsoft ODBC for Oracle" in the list of drivers. on this new PC, i just don't see it.

is there something from Microsoft i need to acquire? or because i installed the Oracle client, are the drivers on my system, but Windows just can't see them? the ODBC driver file is MSORCL32.DLL and is located in c:\windows\system32 and on the new system, i see the file located in c:\windows\syswow64 folder.

i've tried doing a SET PATH=%PATH%;c:\windows\syswow64 from a cmd prompt, rebooting and then trying to add the data source again but the Oracle ODBC driver still does not appear.

can anyone point me in the right direction?
odbc_oracle1.png
odbc_oracle2.png

3 Comments

I am running the following query:

DateAdd("m",Nz(([Inspectionfrequency]*12),0),[Inspectionlastdone])

With the criteria:

<Date()

I am trying to get the number of overdue valves for inspection, but I am getting dates that are later than today. Inspection frequency is in years and sometimes includes decimals and inspectionlastdone is a date in the format MM/DD?YYYY.

Any help would be gladly appreciated!!

Microsoft Access

3 Comments

Hi,
I need to allow an user to access remotely to his Windows 10 workstation.
Workstation is joined on 2008 active directory domain.
Remote Desktop service is enable on workstation (I mean: 3389/TCP port is open, and DOMAIN\user is alreay allowed to access).
"user" is alreay member of "remote desktop user".
When he try access, he get "To log on to this computer, you must be granted the Allow log on through the Terminal (or Remote Desktop) Services right...."

I definitely not understand that.. I think this should be very straightforward (what is the meaning of "remote desktop user", if members still NOT have access )?

Thank you very much

4 Comments

Hi,

I have different form's that i develop on my PC that are according to my screen , when the user open on their screen it shows according to their screens like smaller. I want that the form should fit into different screens. I understand that their is property called "Fit ti Screen" but that's somehow not working. is there a code through which my form should fit into different screen or any other way to address this issue.

Any help/idea would be helpful.

Thanks in advance.

14 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

Microsoft Access

217K

Solutions

209

Content

51K

Contributors

COURSE of the MONTH

Microsoft Access

Related Topics

Microsoft Access

Related Topics