Microsoft Access Help

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

2 Comments

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

3 Comments

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

6 Comments

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?

2 Comments

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?

3 Comments

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.

Thanks for any help.

Microsoft Access

7 Comments

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?

Microsoft Access

13 Comments

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.

9 Comments

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).

8 Comments

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?

2 Comments

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.

12 Comments

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

Microsoft Access

5 Comments

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

5 Comments

LVL 6

Question by

bfuchs

5d agoSolvedPrivate

Correction of date field

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

11 Comments

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?

5 Comments

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

Microsoft Access

9 Comments

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?

Microsoft Access

9 Comments

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

…

1 Comment

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

Microsoft Access

5 Comments

Become a Microsoft Certified Solutions Expert

LVL 13

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?

Microsoft Access

2 Comments

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

11 Comments

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

1 Comment

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?

5 Comments

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

…

5 Comments

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

6 Comments

Microsoft Access

Related Topics

Microsoft Access

Related Topics