Microsoft Access

220K

Solutions

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.

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

Sign up to Post

Hi Experts,

We have a table containing the following fields
PatientID
DoseID
RouteID
FrquencyID

Since all these corresponds to ID of another table, and we don't have access to that table.

Wondering what is the easiest way to determine the meaning of those values from looking at each particular PatientID record on the original system (where there I can see the names w/o the ID's)?

See attached how data looks in the table.

Thanks
Untitled.png
0
Become a Microsoft Certified Solutions Expert
LVL 12
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Hi

When I try to format data set on excel sheet using below MS Access VBA script, I am getting run time error 1004, "Unable to set the HorizontalAlignment property of the Range class.

Public Sub North()
Dim ObjExcel  
   
    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\aa\bb\North.xlsx"

    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets(2)
    ObjSheet.Activate

         ObjSheet.Range("1:1").HorizontalAlignment = xlCenter
         ObjSheet.Range("1:1").VerticalAlignment = xlCenter

Please have a look

Thank you
A
0
I am looking for advice on how to optimize my code.  It is working now, and happily generates the output shown-- but at this moment, I've only written four levels.  I need ten total, and it seems to me that I should be able to reuse the lines of code by passing them different values, depending on what part of the hierarchy has focus at any given time.

This is the code that I would want to re-use;  XX and YY would be replaced by integer values:

    'LevelXXCheck:
    If DCount("*", "HSTProductionReportTb", "ManagerID = '" & rsYY.Fields("EagentID") & "'") > 0 Then
        Set rsXX = db.OpenRecordset(strSQL & XX & " and ManagerID = '" & rsYY.Fields("EagentID") & "'")
        rsXX.MoveFirst
        Do While rsXX.EOF = False
        strInsertSQL = "Insert into HSTProductionHierarchyTb (AgentNa, EAgentID, NAPXX, TeamNAPXX) select '" & rsXX.Fields("AgentNa") & "', '" & rsXX.Fields("EagentID") & "', '" & rsXX.Fields("NAPSum") & "', '" & rsXX.Fields("TeamNAPSum") & "'"
        DoCmd.RunSQL strInsertSQL
    'Insert Next-Level SubordinateCheck here
'end of LevelXXCheck:
        rsXX.MoveNext
        Loop
        rsXX.Close
        Set rsXX = Nothing
    End If

I'll be grateful for any advice or recommendations.  :)

Paul


Example of formatted output

Private Sub btn_Hierarchy_Click()
'loops through the Production table, selecting the top-level manager, that manager's subordinates, and so on down the chain
'names, IDs, NAP, and TeamNap are inserted into a 

Open in new window

0
Is it possible to grow a text field on a form?  I know you can on a report but I don't seem to be able to make it happen on a form.
0
I have a query that lists last names of people.  But I need to show them on a form with the names followed by a comma and a space.  So for example... Steve, Bill, David, George, Susan, Martha, etc.  How can I do this?
0
I have set up a Delete query in Access.  I am trying to delete records from the tblGLDetails table.  The criteria is based on a date stored in the DateCreated field in the tblExtractDate table.  There is only one date saved in the tblExtractDate table.

The records are displayed when I select View; however, I receive the error “Could not delete from specified tables” when I run the query.

Thank you in advance for your help.

The following is the SQL code for the query:
DELETE tblGLDetails.*, tblGLDetails.DateCreated, tblGLDetails.Senario
FROM tblGLDetails, tblExtractDate
WHERE (((tblGLDetails.DateCreated)>=[tblExtractDate]![DateCreated]) AND ((tblGLDetails.Senario)="Actual"));
[/indent]

The following is the Design view of the Access Delete query:
Screenshot of the Access Design View of the Delete query
0
When I create a new record using a form, I have a field that I want to auto populate with the current year and a 5 digit number.  So, for example, it I am creating a record for this year I want the field to indicate "201900001".  And the next record to be 201900002", etc.
0
In open office how to insert one shape inside other

as attached i have big rectangle
I like to insert small circle into that rectangle
how can i achieve it

please advise
OpenOfficeShapes.png
0
visio 2013 standard i opened blank diagram,

I do not see any shapes like circles, rectangles, person, arrows etc on the left hands side

how to enable them and keep them there all the time so that whenever i open visio i can just drag and drop and make the diagram
please advise
0
I am having an issue when exporting Access Reports to PDF.  I am using the 'DoCmd.OutputTo acOutputReport, stdocnameTrend, acFormatPDF, FileNamePDFDets, , , , acExportQualityPrint function.  

The function is working however when I open the PDFs not all of the images are showing, I have looked at the "Display When" and they are all set to always.  When I print to pdf it works, but I can not find the code to automate printing to PDF...

Anyone have any success with this?
0
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

I'm getting so tired of x' ing out of all of those visual basic windows in MS Access code view.
Had the same problem with A2K.. now using Office 365... have they added a function key or key shortcut for this yet???
I can't believe you experts deal with this problem..do you?

Thanks..p
0
i see some templates here.
i downloaded zip and unzipped and copied those contents and placed at
C:\Users\xyz\Documents\My Shapes

http://softwarestencils.com/uml/index.html

any other visio similar templates to reuse existing shapes and sequence diagrams etc

please advise
0
I am trying to list files from folder\subfolder from this sharepoint folder.
SharepointAddress = "https://centralhub.XXXXX.com/team/CHS/SRS/Account Set Up Templates/"
I would like to do this from a button on a form and put the information in a table for exporting.
I have never done anything like this before thru a network except normal dir stuff.
Does anyone have any code that can help me do this. I would greatly appreciate it.
0
is there an easy way in access, to merge all data in all fields into a single list? I have received some data and it looks like 95% of the cells in most columns are empty, but I need an automatic way to merge all data from all columns which are not blank into a single list of data/column of data. Any suggestions if this can be done? there are over 60'000 rows of data, the first column is full of entries, but then there are dozens of fields and 95%  of them seem to be blank, so merging all cells in the table from all fields, where the cell is not blank would be a great help. Is there also a limit on the number of fields you can import into access?
0
Hi,

I am trying to automate report (excel spreadsheet) production using access vba script and windows task scheduler.
I am getting error message at beginning of the scripts itself(given below).

SetAttr "Z:\Report_New.xlsx", vbNormal

(because I apply SetAttr "Z:\Report_New.xlsx", vbReadOnly at the end of all scripts.)


"Z" is a mapped shrepoint drive. The run time error 75 tells Path/File access error. But when I run the script manually there is no issue. When I left it to run over night, vba editor throws this error message.

Please let me know  if you have any suggestions
Thank you
A
0
With Windows 7 and Office 2010 coming relatively close their support life-cycle end dates, our company are planning to move to supported releases, e.g. windows 10 and latest version of Office. We have a number of word macro enabled documents (docm) for workflows, as well as an Access Database application with front end purely built upon 2010. I am not a developer myself, but I suspect major upgrades such as completely new OS and Office versions may have adverse effects on these systems. Are there any general best practice guidelines on making the transition as smooth as possible specific to access databases to ensure they work as expected in the new releases, any specific tools that can be used in the testing etc. Or any general tips on such an exercise based on experience most welcome.

As a general observation, have you encountered many issues with word docm/access database applications when they were developed in a previous version of word/access, when you upgraded to a completely new version of word/access? Can you provide some examples of the scale of the issue, or was it relatively 'painless'.
0
Hi

I am working with a tab control in Access. I have a really long form that I have split into two separate forms so that the user doesn't have to scroll through but has a next button at the bottom. This works well however as the forms are bound to the same table, when the user goes to the 2nd form it adds another record. I did have this working when I had the data in a Access back end but now I have updated to SQL back end it isn't working.

Am I missing something or is this not possible?

Thanks in advance :)
0
Can't load any active x objects
screen-shot.png
0
Hi Experts,

I have a SQL view that is being linked to my Access app.
That view has a column named SNVNum, defined as follows
CAST(REPLACE(dbo.Skilled_Nursing_Visit_Note.SNV_ID, 'snv', '') AS int)

Open in new window

Now when I open the view and sort by that column it works perfectly fine in db container.
it also works fine when running the following in SSMS.
Select * from vw_SkilledVisitNotesFilterFrm Order by SNVNum

Open in new window

However when I assign the form the following SQL as record source.
        Me.RecordSource = "Select * from vw_SkilledVisitNotesFilterFrm  Order by SNVNum"

Open in new window

it gives me the error attached.
Untitled.png
0
Acronis True Image 2019 just released!
LVL 1
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

I'm using Access 2K... but may soon be upgrading to Access 2016 or Office 365 so please do not let my current platform restrict your responses.
Obviously a solution that works with A2K is preferred.

My customer list now has more than 30,000 business email addresses.  These are all addresses were used to place orders, so at some point we know that all of the addresses were valid.

I would like to start a Mailchimp mailing campaign so I need a way to clean the list before attempting a mailing.  Can anyone recommend some code or an Access plug in.. or any other solution that can become part of my Access application... that will allow some sort of programmatic testing to mark hard, or possibly soft, bounces?

If not, any recommendations for a cleaning service would be appreciated.

Thank you.
0
I'm using MS Access and I am using the MS Access 2K application on an XP computer.  When I copy the mdb to a Win10 computer running Office 365 I get the same problem.

When I launch the application and try to edit a form I (occasionally) get a message that states:

You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later.

I know I should have exclusive use.... proven by the fact that I exit the application the .ldb lock file automatically deletes.

Compact and Repair does not help.  Can any of you Experts help me?

Thanks
p
0
Hello Experts!

I'm running a Access 2K app that runs perfectly on XP-Pro computers with 2GB of memory on an WIn10 Machine using MS Office 365.

When I attempt to run a query with 800,000 records I am getting an Memory Resources Exceeded message and system operation fails.  I need to use task manager to get out of the loop.  The same query works fine if I perform a reboot and run the query as first-thing.

I'm obviously running out of something, but what?  Is it system memory, hard drive space, temp file?  What can I do to get this query fixed?

Thanks in advance!

p.
0
Hi,
The screen is Shrinking when run my app on laptop always. So it won't show the bottom part of application on the laptop screen. Look my images may it will clarify my problem.
please help me.
screen One, is what should I see.
screen One what should I seeScreen Two, what happen when I run my application on a laptop, where you can see the buttons down in the form is not shown
screen Two
1
Need help summing every record based on RO_NUMBER the STI.QTY per record using the below statement.

Example:

RO_NUMBER            rdo_auto_key    STI.TI_TYPE    STI_QTY
     879                              1234                       T                   1
     879                              5468                        I                  -1
     879                              9876                        I                   1

Results:
     879                                                              T                  1
     879                                                              I                  -1
                                                             

select rod.rod_auto_key,roh.ro_number,rod.entry_date,rod.qty_repair,rod.qty_repaired,rod.VEND_INV_METHOD,wob.activity,woo.si_number, STI.TI_TYPE, sti.QTY
from ro_detail rod
inner join ro_header roh
on rod.roh_auto_key=roh.roh_auto_key
inner join stock_reservations str
on rod.rod_auto_key=str.rod_auto_key
inner join stock_ti sti
on str.stm_auto_key=sti.stm_auto_key
inner join wo_bom wob
on sti.wob_auto_key=wob.wob_auto_key
inner join wo_operation woo
on wob.woo_auto_key=woo.woo_auto_key
where rod.VEND_INV_METHOD='C' and wob.activity='System Exchange' and rod.qty_repaired=0 and sti.ti_type='T' AND sti.ti_type='I'
group by sti.QTY,rod.rod_auto_key,roh.ro_number,rod.entry_date,rod.qty_repair,rod.qty_repaired,rod.VEND_INV_METHOD,wob.activity,woo.si_number, STI.TI_TYPE

Open in new window

0
What is the best way to have selections based on a multi-selection combo box?

Form contains:
Date
HXXID
Type ComboBox
Type Description ComboBox

I have 3 types:
Type 1 (Has about 15 Choices)
Type 2 (Has about 25 Choices)
Type 3 (Both )

If I chose Type 1, I only want the data from Type 1 to show up in a multi-selection combo box to show up and update the data table.
If I chose Type 2, I only want the data from Type 2 combo box show up in a multi-selection combo box and update the data table.
If I chose Type 3, I want the data from Type 1 & 2 combo box to show up and update the data table and identify with Type 3 (Both) for reporting.

The end goal is to have the  table update based on the following:

Table contains:
Date
HXXID
Type
Type Description
Selection of Y/N
0

Microsoft Access

220K

Solutions

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.