Microsoft Access

217K

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

I need to develop a mail merge for my client.  The mail merge will be initiated from my Access 2013 application.   I have done mail merges many times but always with a one to one relationship between the data and the fields on the document.

In this case I need to develop a mail merge that will have a varying number of 'detail' lines in the body of the document. It's not an invoice but an invoice is similar in construct.

There are certain fields that are only on the document once, such as customer name and address at the top.  

In the body of the document, a detail line (Qty Purchased, Product Purchased, Item Price, Extended Price) may appears one or many times.  If the customer only bought one item it only appears one time, if the customer bought 10 items that line will appear 10 times and the invoice may overflow to multiple pages.  

After all of the purchased products print there is some total information and legal chatter.

Is this construct possible in a mail merge document?     I have heard of placing blocks in a mail merge document to accomplish this type of thing but have never done so.

Example:

John Smith                                                                  08/17/17
123 Main St.
Hulu, HI 443434

Items purchased
Qty                Description                                Unit Cost                   Extended Cost
2                    Widgets                                            1.79                                   3.58
0
Veeam Task Manager for Hyper-V
LVL 1
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Imagine a form with five integer fields.

each field can hold values from zero to 1000.

Using code what is the simplest way to find the field with the lowest number that is not a zero
0
Hi -

I am using a form that is based on a query and not in a Table. It is a relatively complex query as I need  to pick up some of the Table columns and calculate values from them, and the new values are shown on the form. But I do also need to correct, using the form, new values.
However the query is yielding the above error.
My query  is constructed from 3 tables as below:

Screen-Shot-2017-08-18-at-22.39.21.png
can anyone help get me a solution?

joao
0
I have a Split Form in Access 2016.  While the column header filters are useful we want a single input to quickly search for a partial match across a number of fields.

What we have done is placed an input and button in the Header (see screenshot below)
The input is called "txtSearch" and the button marked with label "GO"  has an Embedded Macro assigned On Click
The macro is intended to allow a partial match against the Forename,Surname,OtherName and the WHERE condition has the following filter.

[txtSearch] Like "*" & [Forms]![ClientHeaderEDIT]![Forename] & "*" OR [txtSearch] Like "*" & [Forms]![ClientHeaderEDIT]![Surname] & "*" OR [txtSearch] Like "*" & [Forms]![ClientHeaderEDIT]![OtherName] & "*" 

Open in new window

Whether we just include 1 field (forename) or all 3 it simply fails to apply the filter to the form.

So my question is ... is there something wrong with the way we are doing it OR is the split view form not capable of handling the filters in this manner?  And if the answer is ..its not possible with Split Views ... how SHOULD we do it.

2017-08-18_14-59-17.png
0
I have this query it shows the module units each student has taken, I am not sure how to get the highest attainment ([module unit]) for each student

SELECT TblModules.Module, TblModules.[Module Unit], TblModules.[Module Unit Name], TblStudents.Student_ID
FROM TblModules RIGHT JOIN (TblCurrentPrograms RIGHT JOIN (TblStudents LEFT JOIN TblStudent_Session_Link ON TblStudents.Student_ID = TblStudent_Session_Link.Student_FK) ON TblCurrentPrograms.Programmes_ID = TblStudent_Session_Link.Programme_FK) ON TblModules.ModuleNamePK = TblCurrentPrograms.Module_FK
GROUP BY TblModules.Module, TblModules.[Module Unit], TblModules.[Module Unit Name], TblStudents.Student_ID
HAVING (((TblModules.Module) Like "Raz*"))
ORDER BY TblModules.Module;

Open in new window

ee.xlsx
0
Hello,
I have form named IssueDetails. On that form I have button WorkOrder which should close IssueDetails AND immediately open other form named WorkOrder with data from IssueDetails (IssueID) that I just inserted.
IssueID is primary key on IssueDetails and work order saves to query (work orders and issues are linked tables) - so it should take IssueID from work order query.
It works perfectly when I save Issue first, close  Issue form and then open it again and then click WorkOrder.
But it is not working when I open IssueDetails fill it with new data and then click WorkOrder - means it generates primary key for Issue but it is not picking it from query when opening Work Order without closing the form meanwhile.

Please help me to understand what is wrong with the my code:

Private Sub WorkOrder_Click()
If IsNull(Me.Notes) Or IsNull(Me.ServiceTypeID) Or IsNull(Me.EmployeeID) Or IsNull(Me.VehicleID) Then
MsgBox "Please fill ALL fields!", vbCritical, "MISSING DATA!"
    Exit Sub
ElseIf Me.ServiceTypeID.Value = 1 Then
            If IsNull(Me.NextServiceCombo) Then
            MsgBox "Add kilometers till next service!", vbCritical, "MISSING NEXT SERVICE DATA!"
            Exit Sub
            Else         
            DoCmd.Save
            DoCmd.OpenForm "WorkOrder", , , "IssueID=" & IssueID
            DoCmd.Close acForm, "IssueDetails", acSaveYes
            End If
 ElseIf Me.ServiceTypeID.Value = 20 Or Me.ServiceTypeID.Value = 21 Then           
          

Open in new window

0
Hi everyone -- anybody know a quick and dirty way to expose a Microsoft office color picker using VBA in MS Access? I'm looking to open the picker from a command button and then have it return a numeric value that I can store. I've seen a few custom-coded color dialogs but the code is cumbersome and I have got to believe there is some simple solution I'm missing in order to enable this functionality. Any help?

B
0
Guys,
Any idea how do I use this kind of query in Mcsft access ? If I have one text box and I would like to get the text box having data from do.cmd sql. how  could I do that ?

For example how do I use one of the text box to display its records base on the do.cmd runsql below ?

   Public Sub DoSQL()
   
        Dim SQL QtyBorrow As String
   
        QtyBorrow  =
        "SELECT count (*)  " &; _
        "FROM MPCRHB_Fail INNER JOIN MPCRHB_Pinjam ON MPCRHB_Fail.No_Fail = MPCRHB_Pinjam.NoStaff " &; _
        "WHERE MPCRHB_Pinjam.Tarikhpulang Is Null And MPCRHB_Fail.No_Fail=Forms!MPCRHB_User!NoStaff;"
         
    DoCmd.RunSQL QtyBorrow
0
Hello,

I'm trying to get the data from other field that start with 59 and has 24 characters, I used this string:
Formulas: Mid([SEDA Transactions].[Comments],InStr([SEDA Transactions].[Comments],"59"),24)
Get the code but also the #Func! Error.  Please advise.
0
Hello!

I have a MS access database that has 4 tables and one form.

On the main form, called TaskList, there is a combo box field that allows the user to pick a "ITP Requirement" from the "MasterDoc" table and that autofills one of the columns in the TaskList table also called ITP Requirement. That all works fine, but I have another box below the ITP Requirement that I want to autofill with the description that is in column 2 beside the "ITP Requirement" in the MasterDoc table.

I do not need this description box to autofill into the main TaskList table with the selected ITP Requirement, I just want it to autofill the textbox with the corresponding description from the chosen ITP Requirement. The Combo box for the ITP Requirement is named ITP1 and the field that I want to autofill after that (which is presently a text box) is named Des1.

I tried using the following code as the Control Source in the expression builder of the Des1 text box:

=DLookUp("[Description]","SFICode","[SFICode].[ITP Requirement] = " & Str([ITP1].[Des1]))

But it is not working... Does anyone have any idea why? Thanks for the help! I would attach the database but it contains cost codes etc that are confidential. Please let me know if I need to be more clear with my question.
0
Enterprise Mobility and BYOD For Dummies
LVL 4
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

I have a report that uses D lookup in two columns on the report. I want to total the d look up columns at the bottom of each one but when I right click the text box and select total everything is grayed out except count record and count values? Is there a way to total the d lookup columns?
0
Hi Experts,

This is my Query. How can I get the row number in the output.

Thanks in advance.

SELECT Format(QryHamUsage.[Part No UID],'\P0000') AS FormattedPartNo, QryHamUsage.[Part No], QryHamUsage.[Part Mfg], QryHamUsage.[Item Description #1], QryHamUsage.[Actual Qty], QryHamUsage.ID, QryHamUsage.[Cabinet Name], QryHamUsage.[Shelf Name], QryHamUsage.Bin
FROM QryHamUsage;

and the following is the output.


Formatted  PartNo      Part No      Part Mfg      Item Description #1      Actual Qty      ID      Cabinet Name      Shelf Name      Bin
P0001                             32610012      Nord      Motor/Gearbox combo      1      P0001H      Parts Cage      Motor Shelf      3
P0001                             32610012      Nord      Motor/Gearbox combo      4      P0002H      Parts Cage      Bearing Lista      Drawer 3
P0010                             800F-X10      Allen Bradley      Contact block N.O      5      P0003H      Lista Cabinet 1      Drawer 1      2
P0004                             E111A06X3N      Eaton      Contactor      3      P0007H      Parts Cage      Motor Shelf      1
0
I have this VBA code: -
    objCmd.CommandText = "Exec [sp_UpdateSysproDispatch] '" & TxtOrderNo & "', '" & PurchaseOrderNumber & "', " _
    & "'" & SalesOrderNumber & "', '" & SalesOrderLineNumber & "', '" & SysProDespatchNumber & "', '" & SysProDespatchLineNumber & "', " _
    & "'" & DespatchDate & "', '" & StockItemCode & "', '" & QuantityToRecieve & "', '" & Warehouse & "', '" & SageStatus & "', " _
    & "'" & SysProStage & "', '# & DeliveryDate & #'"
    objCmd.ActiveConnection = adoConn_Sage
    Set objRs = objCmd.Execute

Open in new window


But I get this error: -
SP ErrorCan Anyone help?
0
Hello,

I have a form that have multiple listbox with multiple selection and they are filtering subform. Now i want to have filter subform based on date selected in texbox in mainform. VBA code can be use to filter. Also attach is the DB in which frmListBox is the one that have date textbox . When date is selected from that it should filter subform field Timestamp.

Any help would be appreciated.

Thank you.
Wsm93.accdb
0
Hi Folks
I'm not sure why this isn't working. I need to retrieve all records except where a few IDs are input.
I tried NOT IN (0,1,2) and I get all data (no filter).
if I use NOT IN (0) the '0's are excluded (as required), same for NOT IN (1)
is it valid to use a comma-separated set of integers in the IN clause? (or just strings in quotes?)
thanks for any useful comment.
0
I had this question after viewing obtain filenames from a folder in ms/access.

Hi
After pasting in the suggested code, I am getting this error that I can't really understand; I have decompiled my code and when trying to compile I am getting the error.
The below screenshos show the source code
can anyone help?

Screen-Shot-2017-08-16-at-20.45.38.png
Screen-Shot-2017-08-16-at-20.57.57.png
Screen-Shot-2017-08-16-at-20.58.14.png
Screen-Shot-2017-08-16-at-20.58.29.png
Screen-Shot-2017-08-16-at-20.58.57.png
0
I have a query that has as its data source 4 queries. Each of the 4 sub queries result in a single record with one field. I do not have a need to join them. I simply want to list the 4 fields together in one record. The SQL representation would be:
Select query1.field1, query2.field1,query3.field1,query4.field1 FROM query1, query2, query3,query4;
This appears to work.

My question is: This seems unconventional and am concerned that this may not always work. Should I add a field to each query to join on ? Is there another approach ? I have a similar application where I need to list up to 11 fields.

Thanks for the help
0
Guys,
How do we set one or multiple  text box as mandatory to be fill in by user before they can submit the form ? or  pop a message that ask teh user to fill the  information before they can successfully save the record
0
Dear Experts,
I desperately need to make subform with tree view.
Since years managed to avoid same, as numerous examples on the web are beyond my understanding. Some of them tried but never worked (incl:http://www.jkp-ads.com/). TreeviewControl stopped working long time ago.
Is there any "relatively simple" way to do same? Two levels will be enough: Chapter=>Subchapter based on two table: tblChapter (parent) linked tblSubchapter (child). Maybe you can recommend smth?

Thanks in advance
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

I have a form that is a tabbed form within a "master form".  When a particular tab is clicked I want some of the objects to NOT appear but let them appear on the other tabs.

I've tried this but it doesn't work:

Private Sub tabname_Click()

        Me.Child36.Visible = False
        Me.Box31.Visible = False
        Me.prev.Visible = False
        Me.next.Visible = False
        Me.Label28.Visible = False
        Me.Label29.Visible = False

End Sub

Open in new window

0
Hello all:

I am opening a recordset and connecting to an excel spreadsheet using Access 2010.  What I am trying to do is read from it  as well as write to it.  I can query the recordsource (a sheet in the workbook via  SELECT * FROM [Sheets1$] ), but can't write to it.  I was trying to write to it by using the following method:

rs.Fields("ColumninSpreadsheet") = "someValue"
and then performing a rs.Update.  This does not error out, however it does not update the column in the excel file.  Here is some of the code I currently have:
    con.Provider = "Microsoft.ACE.OLEDB.12.0" ' "Microsoft.Jet.OLEDB.4.0"
    con.ConnectionString = "Data Source = " & strFileName & ";" & _
    "Extended Properties=Excel 12.0;" ' "Extended Properties = Excel 8.0;"
    con.Open
    
    strSQL = "SELECT * FROM [" & lstSheetNames.value & "$]"
    rs.Open strSQL, con, adOpenStatic, adLockBatchOptimistic
    
    
    If Not (rs.EOF And rs.BOF) Then
        Do Until rs.EOF
            rs.Fields("UserName") = "userName"
            rs.Update
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
        con.Close
        Set con = Nothing

Open in new window


Can anyone tell me:
1) is it possible to update the spreadsheet this way and if so,
2) how?

Many thanks!

Juan
0
Hi!
I need to programmatically populate a Table with the filenames of a specific folder; a sort of a dir command. How do I do it? Any help will be  really appreciated
0
I have an MS Access Database connected to the application. I want to be able to install the application on several computers. I want to give the users an option to "Compact and Repair" the database. I have included a copy of the database in my resources folder of the application.

I know I need the Microsoft.Office.Interop.Access.Dao.DBEngine, but I am unsure of where to start.

what would be the best way to do this? Should I copy the file from my resources or what?
0
I have a form linked to a sql table (tbl_employee). On the employee table I added a new column called category. Back on the form I created a combo box via the wizard. In the drop down you have three choices. General-Professional, Executive, and Manager. The selection is then stored in the table (category field). I went through a bunch of records and selected the appropriate choices in the drop down. I then verified the selections were being stored in the table. Then I accidentally deleted my combo box. No when I try and repeat the process the combox is blank even though they are still stored in the table. If I click on the combox the choices I re-typed are there but selecting them does nothing. At the bottom of the form it says: control can't be edited; its bound to unknown field category. My control source says category but still no luck. How do I get this back to its original state?
0
I have an existing Access 2010 database with a lot of VBA code.  I'd like to be able to add a couple very simple webforms so that users can submit data to the database but I am not sure how to accomplish this.  I know a hybrid access database can be built but I guess I'm concerned that I would have to start with a "web database" and I dont want to recreate this whole thing. Is there a way for me to add a simple web form for use through my company intranet without recreating the whole database?
0

Microsoft Access

217K

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.