Microsoft Access

221K

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

I have a table called tblAdminForms that holds information on text and images that will be loaded to when the form loads. The following code reads the table when the database loads. Is it possible to assign text to labels and images to image controls for every form and report when the database loads or should I call code on every form load event to load the controls and images

Sub loopTable()

On Error GoTo EH

    Dim strSQL As String
    Dim rs As DAO.Recordset
   
    Dim oFormOrReport As String
    Dim oControlID As String
    Dim oControlText As String
    Dim oImagePathAndName As String
   
    strSQL = "SELECT * FROM tblAdminForms"   'define the SQL result that you want to loop
    Set rs = CurrentDb.OpenRecordset(strSQL)
   
    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        While (Not rs.EOF)
       
            oFormOrReport = rs.Fields("Form or Report Name")  'define the field you want to return data
            oControlID = rs.Fields("Control ID")
            oControlText = rs.Fields("Control Text")
            oImagePathAndName = rs.Fields("Image Path And Name")
           
            rs.MoveNext
        Wend
    End If
    rs.Close
    Set rs = Nothing
   
    Exit Sub
EH:
    MsgBox "Error reading tblFormsAdmin table " & Err.Description
   
End Sub
0
Should you be charging more for IT Services?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi. I am using the following Access code to load all the controls in a Form or Report
to a Combobox. The Form  part works but the Report part doesn't because opening the report
causes it to run and I get the error "The report name you entered is misspelled or refers to a report that isn't open or doesn't exist"

    If sFormOrReport = "Form" Then
        DoCmd.OpenForm CT, , , , , acHidden
        Set frm = Forms(CT)
        For Each ctl In frm.Controls
            If ctl.ControlType = acTextBox Then
                Me.cmbControls.AddItem (ctl.Name)
            End If
        Next
        DoCmd.Close acForm, CT
    Else
        DoCmd.OpenReport CT, , , , , acHidden
        Set rpt = Reports(CT)
        For Each ctl In rpt.Controls
            If ctl.ControlType = acTextBox Then
                Me.cmbControls.AddItem (ctl.Name)
            End If
        Next
        DoCmd.Close acReport, CT
    End If
0
Hello, I am having problems figuring out why I can't get my form to submit registration information to a table in access database. I believe the problem in with the Inner Join.

The user should enter their student ID, and in populates the name and then they select the class from the combo box. The combo box lists the class and the number of slots available for a particular class.  The submit button should grey out if the user selects a class that has zero slots available.  If class is available it should submit to table.

tblRegistered is where the record should be added.

Any help resolving this would be greatly appreciated.

Thanks
TEST_FE.xlsm
TESTDB.accdb
0
Hi. In my MS Access form I want to set the path and file name of an image when the form loads. How do I do this?
0
Hi. I have a form in Access where I want to put a button that when clicked allows the user to browse and select an image. Once they gave selected the image I want to display the image path and file name in a label on the form. What VBA code would I use to do this?
0
Hello,

Simple problem: I want to hide the status bar on a subform in Access (office 365). I don't see the Navigation Control setting in the Property Sheet of the subform.

I have tried:
  • Running Script:   Application.CommandBars("Status Bar").Visible = False
  • File>Options>Client Settings> Checked  the Status Bar box

It has been a while since I developed a form in Access, but I thought this a property of the subform.

Screen shot
Thanks for any help.
0
Can you refer to a forms combobox column number in the update to field in an update  query?

[Forms]![Combo'sForm]![19.Column(1)]  (This doesn't work by the way)

The name of the combo box is 19 (Yes I know but I never do anything by the book. Maybe why I get into so much trouble)

If possible what is the correct syntax?
0
I receive an "Object variable or With block variable not set" error when I try to run the following code in Access 2010:
 Dim objProcess As Object
 objProcess.Start ("C:\WINDOWS\system32\calc.exe")

However, the following code runs in VB.Net:
Dim StartCalc As Process = System.Diagnostics.Process.Start("C:\WINDOWS\system32\calc.exe")

Please help resolve the error.
0
Whats the best way to remove dupes from a table? I have two fields, OrderNumber and CustomerID. There are many customers with more than one order. I want to remove all but one record for each CustomerID (doesnt matter which OrderNumber I'm left with).
0
How do I perform the following tasks on an Microsoft Access database.

Drop three Columns
Add one Column

Do this on five different tables.

Can this be done in a script using ALTER?
0
OWASP: Avoiding Hacker Tricks
LVL 13
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Dear All;

Sorry I thought putting header was just simple in json converted data , but has proved to destroy all my effort so far . I'm now required to rearrange the Json data from screen shoot below to have the following:

(1) The customer name & address MUST only appear on top of the receipt NOT on every item.
(2) List of products must have category called ITEMS


Example

{
"Customer": "J j Zingalume ",
"Address": "Lusaka West",
"Items:[
{
  "Item":"1"
 "Description": "Rice"
"Quantity":"10"
"UnitPrice":"51"
"Total":"510"
}
]
}

Open in new window

Kindly note that Im using the Json converter in  Ms Access, that is why I have these challenges.


DataArrangements.png
0
Variable not defined on tcpClient in Ms Access

How do I declare this thing proper

Private Sub CmdTransferData_Click()
' The name of the Winsock control is tcpClient.
' Note: to specify a remote host, you can use
' either the IP address (ex: "121.111.1.1") or
' the computer's "friendly" name, as shown here.
tcpClient.RemoteHost = "192.168.1.161"
tcpClient.RemotePort = 8080
tcpClient.Connect
End Sub

Private Sub tcpClient_DataArrival _
(ByVal bytesTotal As Long)
Dim dataToSend As String
dataToSend = CmdSales(" ", True)
tcpClient.SendData dataToSend
txtoutput.Text = dataToSend
End Sub 

Open in new window

0
Hello Team

I have a form which contains a tab control consisting of 7 pages. Within Page7 I have 9 checkboxes.  

What I would like to achieve is to have code which on a save event will reference Page7 and loop through each checkbox to see if they have a true or false value.

if a true value is identified from any of the checkboxes then the code can flow through to completing the save event but if all 9 checkboxs  each return a false value then the code will exist sub

Form Name: frmRenewalsAudit
Tab Control name:  TabCtlCats
Checkbox Names:  Chk1, Chk2 etc through to Chk9

Any assistance would be greatly appreciated.

Thank you in advance

Dale
0
hi Experts,

We have a column DOB that was mistake defined as text and we are planning to convert to Date column.
However there are many records with invalid dates in it.
Wondering if there is a function that will correct this when something is obvious, like only missing slashes or removing spaces etc...
See attached for sample data.

Thanks
Untitled.png
0
I have an unbound form frmLogRptCPUSelection that uses a combo box cboComputerDevice to select a device using SQL <SELECT tblComputer.cID, tblComputer.cName
FROM tblComputer ORDER BY tblComputer.cName;>  Once the selection is made I have a "Print" button on the form that has the following code in the Print button Click event.

I also have a report rptEventLogByComputer that is based on a query qryComputerLogCPU.  One problem is that cboComputerDevice is the value of cID not cName.  
I can get the cName with

Dim strID As String
Dim strcName As String

strID = Me.cboComputerDevice
strcName = DLookup("[cName]", "tblComputer", "[cID] = " & strID)

I have tried putting [Forms]![frmLogRptCPUSelection]![cboComputerDevice] as the critera in the query but it does not work.

Now how do I pass strcName to qryComputerLogCPU so that the report will work?
0
Hi

Kindly help me to increase the following time parameters in my query in Ms Access, see the code below:
DocumentDate: DateAdd("d",1,[InvoiceDate])

Open in new window

(1)      The day is okay
(2)      Hours must increase by 24
(3)      Minutes must increase by 1440
(4)      Seconds must increase by 86400
All must be in one code
Regards

Chris
0
Using MS Access 2013, I created a shortcut menu in a subform
           
                    Set cbrButton = cbr.Controls.Add(msoControlButton, , , , True)
                    With cbrButton
                        .Caption = "&Create One-Off deliverable"
                        .Tag = "AssignOneOffDelierable"
                        .OnAction = "=fnAssignOneOffDeliverable()"
                    End With
                   
I placed the function in the parent.  that has worked well until I changed the parent to .Popup=YEs

now I get error unable to find function?
0
After going through Ms Access VBA  line by line to try and discover why my posting is not  being recorded on the fake web site I have discovered a clear error  on

 Set JSON = ParseJson(http.responseText)

Open in new window


See my full code here:

Private Sub CmdSales_Click()
  
'  Const SQL_SELECT As String = "SELECT * FROM Qry3;"
  Dim http As Object
  Dim JSON As Object
  Dim coll As VBA.Collection
  Dim dict As Scripting.Dictionary
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Set http = CreateObject("MSXML2.XMLHTTP")
  Set db = CurrentDb
  Set qdf = db.QueryDefs("Qry4")
  http.Open "POST", "http://jsonplaceholder.typicode.com/comments, False"
  http.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
  http.setRequestHeader "Content-type", "application/Json"
  http.send ("ConvertToJson(coll, Whitespace:=3)")
  Set JSON = ParseJson(http.responseText)
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()

Set qdf = Nothing
  Set coll = New VBA.Collection
'  Set db = CurrentDb
'  Set rs = db.OpenRecordset(SQL_SELECT, dbOpenSnapshot)
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
      Set dict = New Scripting.Dictionary
      For Each fld In rs.Fields
        dict.Add fld.Name, rs.Fields(fld.Name).Value
      Next fld

      coll.Add dict
      rs.MoveNext
    Loop
  End If

  rs.Close
  Set fld = 

Open in new window

0
Hi, what is the best way to add a new record to both a Main Form and a Sub Form? I currently use:

  Me.xMainText.SetFocus
  Forms!MainForm.Form.Dirty = True
  Forms!MainForm!SubForm!xName.SetFocus
  Forms!MainForm!SubForm.Form.Dirty = True

This will create a temporary record in both main form and subform. Is there an easier way? Second what is the best way to SAVE both records (Main and Subform) WITHOUT closing the form?
Thank you so much for any help. Rg Michael
0
Become a Microsoft Certified Solutions Expert
LVL 13
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

I am looking at an Access table where I cannot see the password. What type of field is this? Is there a way to see the password?

1
0
Hi there,

I haven't used Access in a while and I'm trying to help someone make some changes to their current Access DB forms.  In a few forms, there is a border around some fields, which is preventing me from moving the fields around or even insert new ones.  Attached is a picture of what I'm seeing.  Can someone please help??  I'm stuck!

Thanks,
- Christian
Access-Border.JPG
0
I want to send the data from Ms Access VBA/Json to a general website which does not require authorisation, but the data does not go there below is my "POST" method:

Option Compare Database
Option Explicit
Private Sub CmdSales_Click()
  
'  Const SQL_SELECT As String = "SELECT * FROM Qry1;"
  Dim http As Object
  Dim coll As VBA.Collection
  Dim dict As Scripting.Dictionary
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Set http = CreateObject("MSXML2.XMLHTTP")
  http.Open "POST", "http://jsonplaceholder.typicode.com/invoices,False"
  http.setRequestHeader "Content-Type", "application/Json"
  http.send ConvertToJson(coll, Whitespace:=3)
  Set db = CurrentDb
  Set qdf = db.QueryDefs("Qry1")
For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()

Set qdf = Nothing
  Set coll = New VBA.Collection
'  Set db = CurrentDb
'  Set rs = db.OpenRecordset(SQL_SELECT, dbOpenSnapshot)
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
      Set dict = New Scripting.Dictionary
      For Each fld In rs.fields
        dict.Add fld.Name, rs.fields(fld.Name).Value
      Next fld

      coll.Add dict
      rs.MoveNext
    Loop
  End If

  rs.Close
  Set fld = Nothing
  Set rs = Nothing
  Set db = Nothing
  Set dict = Nothing
  MsgBox "Post Success"
  Set coll = Nothing
End Sub

Open in new window



Just also in case of the username/password required where do I put it in the code above?


Regards

Chris
0
I have no problem when I send data from a recordset to an array using recordset.GetRows method in ADO, my problem comes when I try to make the same with DAO, the GetRows property only "pulls" the first record from the recordset, and I want to pull all the records from the recordset without using a loop.

I have tried the following with no success:
recordset.GetRows()

Open in new window


and

recordset.GetRows(.RecordCount)

Open in new window


Any guess?
0
We are now required to process the invoices from Ms Access and then transfer the invoices details to the tax authority in Json format , fair enough . All is okay except the date is always behind by a day after using the Json converter below.

(1) I have attached the screen shoot for the actual invoice dated 13/09/2019 and the converted data into Json, Kindly check the DATE it is always behind by 1 day. Is there a way to sort out this?


'
VBA-JSON v2.3.1
' (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON
'
' JSON Converter for VBA
'
' Errors:
' 10001 - JSON parse error
'
' @class JsonConverter
' @author tim.hall.engr@gmail.com
' @license MIT (http://www.opensource.org/licenses/mit-license.php)
'' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '
'
' Based originally on vba-json (with extensive changes)
' BSD license included below
'
' JSONLib, http://code.google.com/p/vba-json/
'
' Copyright (c) 2013, Ryo Yokoyama
' All rights reserved.
'
' Redistribution and use in source and binary forms, with or without
' modification, are permitted provided that the following conditions are met:
'     * Redistributions of source code must retain the above copyright
'       notice, this list of conditions and the following disclaimer.
'     * Redistributions in binary form must reproduce the above copyright
'       notice, this list of conditions and the following disclaimer in the
'       documentation and/or other materials provided with the 

Open in new window

0
Hi team,

I have been working with Windows 10 x64 1903 and SQL Server 2014 trying to get the attached openrowset script working.

When it is reading from the database on the local drive C:\QWTEST\docs.mdb, it works fine.  

However when I substitute N:\QUOTEWERKS\docs.mdb for  C:\QWTEST\docs.mdb  I get these two error messages:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Note:

I have added  a 32-bit ODBC connection for C:\QWTEST\docs.mdb and connect to this correctly with Crystal Reports.

When I try to add an ODBC connection for N:\QUOTEWERKS\docs.mdb, I cannot see the mapped drives in the available drives list.  This is a known issue with WIN10 ODBC connections and the only workaround, is to first create a local drive location, save it and  and modify the registry line for the mapped drive.  This works.

I thought there might be the same kind of solution with accessing the mapped drives.

All assistance is appreciated.

Cheers
Bernard
OpenRowSet.sql
0

Microsoft Access

221K

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.