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

Hello,

I have a Main form and 3 subforms.  Each of the 3 sub forms has more sub forms.  When I initially open the Main form, I do not want to load any of the 3 sub forms or their subforms.  I also have 3 buttons inside the Main form.  On the click of these buttons, I want to open the corresponding subforms and its subforms.

Thanks much in advance for your help
Joy
0
Does anyone have any favorite icon packs for modernizing older Microsoft Access databases?
0
Hi Experts,

I'm trying to insert a record from Excel into an Access table and getting an error "Record is too large", see attached.

This table has many fields, any idea how can I figure out which column is causing it?

Thanks
Untitled3.png
0
I am using an access database to handle scanning orders into folders as a PDF.  

The scan function works great.  It drops the image into a local file as a jpg, no problem.  
I open the jpg(s) in a report, which works.
I close the report, using acSaveYes, and this seems to be working correctly.
Where I run into issues (at least on the AccessRuntime side of things) is in this line

DoCmd.OutputTo acOutputReport, "rptScan", acFormatPDF, "C:\TempScan\Report.pdf"

In the Runtime environment, I frequently get a 2046 error, and it stops.

If we rerun the process (sometimes multiple times) it goes through with the exact same results.

I added in a statement to try to get around this, by having it loop back to the portion of the code above in the Handle_err

    Select Case Err.Number
        Case 2046
            GoTo StartPDFConversion:  {the line above}
    End Select

Rather than loop, its still coming up with the 2046 error.    

I am using RT2013 as I was seeing too many bugs with RT2016.  

Is there a better way to force a retry of the PDF outputting code so that it just retries again?

Any help would be greatly appreciated.

Jeff
0
Hello,

I have a form with a button on it to open another form based on 1 criteria for OpenArgs.
I need to add another criteria [Trade_No]....its a string. I am not sure if being a string is my problem.

I get a runtime error 3075 (syntax) and the debugger highlights the code in the form that  is opening.  I hover over [Buy_CP] and the value is correct but [Trade_No] is also the same value as [Buy_CP] and that is not accurate.
Below is the error::

RuntimeError
I have the following in the form that has the button:

Private Sub btnIssueLC2_Click()

      DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , Form_subfrmProjects_Extended_List.Buy_CP & ";" & Me.Trade_No & ";"
    
End Sub

Open in new window


In the form that opens:

Private Sub Form_Load()

       If Nz(Me.OpenArgs, "") <> "" Then
       
            Me.Filter = "[Buy_CP] = " & Me.OpenArgs & " AND [Trade_No] = " & Split(Me.OpenArgs, ";")(1)
            Me.FilterOn = True
       
           Else
     
     End If
End Sub 

Open in new window



if I missed something let me know....Thank you
0
I have a form [f-BagLabel] on to which I would like to place a "Print Report" Button.  A similar report [r-BagLabel] already exists, and its based on a query [q-r-BagLabel].  In this case, however, I want the PrintReport button to print a report specific to the current record at [f-BagLabel]

Ordinarily, I would copy the report and copy the query, associate some query criteria from [f-BagLabel] into the new query and associate the new copied report with the new query w/ criteria.  Then, I would add a button that would open this newly created report.  This, as I am sure you are aware, results in multiple instances of the similar queries and reports.  I need to learn how to use conditions in VBA to accomplish this task w/o resorting to my current technique.

How would I incorporate some VBA into this Button to print a report whereby a field in q-r-BagLabel = some value on the form where the button is positioned?

Thanks,
Ken
0
Hi,

I have a subform in Ms-Access, with the following information:

Product ID, Description,  Quantity,  Price, Total (Calculated field).

At the bottom of the form I have a Grand Total calculation.

However, when I change the Quantity, the Grand Total is not updating one time.  It is when I press enter(s) until a new record that the Grand Total is updated.

I know that "me.refresh" or "cmdruncommand accmdrefresh" after update (event) Quantity could be a solution.
However, if if use the "Refresh command", the form blinks and it does not look professional (at least for my point of view) when a user types information and the blinking blinking appears.

Is there any other way of getting the Grand Total updated, avoiding the blinking?

Thank you,
0
i would like to extract the first Rem that has a space after from the string in regex
 "RemLETS BUILD THE': Rem Room"
in the example Room should be the extracted word
 "Rem LETS BUILD THE': Rem Room"
in the example the entire sentence from LETS BUILD THE': Rem Room"
ignore case false
0
Hello Team


Can you please advise on the syntax that would be required to filter a query from selecting a true or false value of a checkbox.

Form Name:      frmAnalytics
Query Name;    qryRenewal
Checbox Name.  chkNoErrors
Reference Field:  TotalErrorCount

The checkbox purpose is to allow the user to select whether or not the qryRenewals is to be filtered with records that show all records (false checkbox value) or to only show records that have a positive Total Error Count value which means excluding any Null or 0 entries in the Total Error Count field.(true checkbox value)

In the qryRenewals I already have criteria filters that are driven by the selections of the frmAnaIytics form and would like to add the checkbox filtering using the same method.  For example, I have a filter field in the query which filters by which team is selected from a drop down control on the form using:

IIF([Forms]![frmAnalytics]![cboUser]<>"",[User]=[Forms]![frmAnalytics]![cboUser],True)

The field holding the above formula has a '<>False' query criteria applied.

As always, any assistance is much appreciated.

Sincerely

Dale







I  have form  which is used to filter various queries and export for reporting.

The form allows the user to select via a drop down control a specific query to filter, for example qryRenewal.


What I would like to do is, allow the user the have the option to select a checkbox to filter out records that contain any null or 0 …
0
I have a simple crosstab query in access 2016 which uses a field called WeekEnd as the column heading which is a date field. So each pupil in my list has an attendance rate for end of every week. I have used conditional formatting to highlight in red those pupils whose rate is below 85%.  What I would like to do is format in red if the attendance rate is below the previous value or green if is above the previous value based on the date. Is this possible?attendance rate
Also noticed that my form does not dynamically update when I add in a new week's data. Its in the crosstab query but does not automatically transfer to the form.

Any help appreciated, many thanks in advance.
0
Hi

What VBA code would I use to detect any table or column differences between two Access databases. I am looking at two versions of the same back end and constantly need to check this

Thanks
0
need calculation for avg number of  counts per day.

I have 3 fields

Create_date
status  - text
nsc_id  - primary id autonumber


I have about 30,00 records in table
i.e. below


12/1/2019 7:11:43 AM       completed         1
12/1/2019 7:00:43 AM       completed         2
12/1/2019 7:12:43 AM       completed         3
12/2/2019 7:05:43 AM       open                   4
12/2/2019 7:11:43 AM       completed         5
12/2/2019 7:00:43 AM       completed         6
12/2/2019 7:12:43 AM       open                   7
12/2/2019 7:05:43 AM       open                   8

What I need:

I will be looking at all 2019 records so i need to know on the avg how many status that are "completed" for a given day


on 12/1   3 were completed
on 12/2   2 were completed

so on the avg 2.5 are completed in a given day



Thanks
fordraiders
0
MS Access 2016 - I have a LIST BOX and the user wants to be able to Select multiple entries using the SHIFT Key. Example -1 to 100 entries.  He wants to select 20 to 30 with the shift key, and 50 to 55, etc.
0
Experts,


In the below code I am trying to reference the Parent and subform.  The error I get is object required.  [Buy_CP] is located in parent frmprojectDetails and subform: subfrmProjects_Extended_List.

DoCmd.OpenForm "frmLetterOfCredit", acFormDS, , , , , OpenArgs:=frmprojectDetails.subfrmProjects_Extended_List.Buy_CP

Thank you
0
Hey experts,

In Access 2016, I didn't get the weeknumber with function WeekDayName or Weekday.

How can I Get the week number in a column from another column which has the given date?
0
Hi Experts,

Is the below correct code to reference a subform: subfrmProjects_Extended_List
I get a syntax error.
The code below is on a button on the Parent but I have added a subform and the [BuyCP] is now on the subform.

DoCmd.OpenForm "frmProjectDetails".subfrmProjects_Extended_List, acFormDS, , , , , OpenArgs:=Me.Buy_CP


Thank you
0
Hi,
How can I avoid ms-access form from Blinking?
I have an Invoice (FormInvoiceHead), with some related fields: Customer, date, address....
Everytime I select the Customer from a dropdown list, the form is updated and in the process it starts (the form and sub_form) blinking.  And it does not look professional.

Note:   I don't have any requery or refresh code after update.

Please advise,

Thank you,
0
Experts, when you group in a query, you can not update the record source?  Is there some work around for this or can I remove the grouping in the record source and group on a form instead?  I dont see grouping functionality on forms like I do see in reports.  

thank you.
0
Hi Expert,

In Access 2016, how can we copy records from one field to another in the same table, with a macro?
0
Hi Experts,
Is any way I can enable the "More" option next to the "PDF or XPS Datain the print view in office 365 access 2016 run time?  I would like the users have ability to merge the report to word but it only works on full version not in run time.  If possible to enable it in runt time version?  I attached the screen shot.

Thank you,
Report.jpg
0
I have an Excel spreadsheet that we display on large TV screen. It looks great all the columns are aligned to fit on the screen (in portrait mode).

The spreadsheet does an access update every 5 minutes. This works great too.

But when the spreadsheet updates it will adjusts the column widths to largest field in the access database and then the spreadsheet will not look correct on the screen anymore. Usually it will shrink the row down. I 've tried to password the sheet but then access will error with message it can't update.

Any Ideas on how to control this. I don't want to create a blank record that will have the right amount of spaces it might look funny have 1 blank line displaying.
0
Hello Team

Can you  please advise how it is possible to display the value of a text box to displayed in a combo box.

I have a form called frmAuditors which contains amongst others, a combo box called cboCSRLookup and a text box called txtWindowsID.

The combo box has is populated from a table called tblAuditor which contains four fields with the bound field being the first column [ID] field. The combo box actually displays the value in the Auditor Name field.

I have used the following dlookup to obtain the ID number from the tblAuditor table.

CSR = Dlookup("[ID]", "tblAuditor", "[WinID] = '" & me.txtWindowsID& "'")

The above dlookup provides the ID number which can be used to corresponded with the ID field that the combo box is bound to.

Can you please advise how it is possible, to display the in the combo box, the Auditor Name that is based on the ID value obtained from the above aforementioned dlookup? Alternatively, if there are any other or better methods of obtaining the required result, your advice would be much appreciated.

Just as a quick example:  If I input into the text box MXTHOM, this string value has an associated Auditor table ID value of 20.  This ID value will be available to the combo box via the row source. So the objective is to pass the ID value of 20 to the combo box and the combo box will then display the associated string value (item) MXTHOM.

Thanks in advance.

Sinerely

Dale
0
I'm getting the following error after upgrading to a new computer:

"The code in this project must be updated for use on 64-bit systems.  Please review and update Declare statements and then mark them with PtrSafe attribute."

This is the code that is failing:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

How do I fix this?
0
Hello Experts,
I have a Form, SubForm, and SubSubFrom.  After entering data into a control on the SubSubForm ([Qty]), I would like Access to automatically create a new record for the SubForm, and return the cursor to the [Qty] field on the SubSubForm.

How can I accomplish this?

Thanks.
Ken
0
I'm almost done with serial port data format parameters in Ms Access, now I need help on the last part of parameter listed below where the length byte is given ? how to treat it in binary conversion:

Fied                                                                     Length(Byte)

Content                                                                                        ?

so what do I put in the converter below under Length ????????? ( Could it be 0 or does ? represent somthing?):

Option Compare Database

Option Explicit

Public Function DecToBin(ByVal lngNumber As Long, Optional bytLength As Byte) As String

' Returns string that represents the binary expression for lngNumber.
'
' If bytLength is specified, returned string will be filled with
' leading zeroes up to this length.

  Dim strBin As String
 
  While lngNumber > 0
    strBin = (lngNumber Mod 2) & strBin
    lngNumber = lngNumber \ 2
  Wend
  If bytLength > 0 Then
    strBin = Right(String(bytLength, "0") & strBin, bytLength)
  End If
 
  DecToBin = strBin

End Function 

Open in new window

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.