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

I developed a very simple Db for a client to replace an older app.  I used my PC, running Win 10 PRO and Access 2016 full version.  The same app runs OK on the clients Win 10 PRO machine with Access 2016 runtime EXCEPT when running any report, I get a fatal exception and the app has to close.  I tried 32b and 64b 2013, 2016 versions, all the same result on the client's machine.  Any ideas?  I don't want to load a full version on the client machine, set the code, try it then uninstall it - just yet.  Not if there is a "head slapping" DOH! I am missing.
0
I had this question after viewing How do I extend the length of a form in MS-Access.
0
I have a document with multiple tables.  The next to last table needs to have the width of the 3rd column set so that it is the width of the text in the column so that the single element in the column doesn't get cut across 2 lines.  For example, one row has something like
C2AJ_YZ-10-AB-002.  Currently, this column cuts the string after the first hyphen, but I'd like the column to fit the text in one row.
   My first issue is how to access the correct table.  (This table will always be the next to last one.)  Then, how do I set the width of the whole column to be the same width as the text in that column?

Chris Cote
0
Hi Experts. I have three textboxes which only show when a button is checked then subsequently the values in the text boxes are summed up. My existing solution is returning empty using   ![Bill] = Nz((Me.txtPharmAmt1 + Me.txtPharmAmt2+ Me.txtPharmAmt3), 0).
0
I have a form I built in Sharepoint 2010 tied to a List.  I have version control turned on and for one key field I utilize the Append function to track history.  This is great, however when I export I am aware of a known limitation that only the last update is exported.  My challenge is that I have users that update this key field and then come back and make a change to the form (not including the key field).  This results in blank data when exported.  

Question: Is there a way to include detail from the last 2 versions when exporting to excel?
0
Hi,

I am Rupesh & I am new to this forum.

I am trying to build a tool in excel which can update database maintained in access.

There will be 3 to 4 user for this excel tool which will be connected to one access database.

The excel will act as the front end tool & access as a backend tool.

I want excel VBA to perform 3 things:

1. It will update data in access based on 2 column which will act as a unique code.
2. It will add any new row if the data doesnt match with previous unique code.
3. If the user wants to delete perticular row / range then it will be deleted from database also.

Below sample of table:

Country      Product code      Sub-Brand      Mfg Base
Russia      SRU030008869020137      Syrups      Own
Russia      SRU030008869020141      Syrups      Own
Russia      SRU030008564220158      Tablets      Own
Russia      SRU030008564220258      Tablets      Own
Russia      SRU030008564220558      Tablets      Own
USA      SRU030008869020137      Syrups      Own
USA      SRU030008869020141      Syrups      Own
USA      SRU030008564220158      Tablets      Own
USA      SRU030008564220258      Tablets      Own
USA      SRU030008564220558      Tablets      Own

1st two column will act as a unique key.

We cannot use a replacement code since there will be multiple user working on different region, replacement option can change  or erase others database.

Thanks for the help in advance.

Regards,

Rupesh.
0
Hi,
 I want to upload my excel data file to access using button event. Below is my code.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim Conn As OleDbConnection

        Dim Cmd As OleDbCommand

        Dim Filename As String = "D:\Ayyappan\claimDump.xlsx"

 

        'Try

        Dim StrFileName As String = String.Empty

        Dim XLA As New OleDbDataAdapter

        Dim XLTable As New DataTable

        Dim StrSelect = "SELECT * FROM [{0}]"

 

        OpenFileDialog1.FileName = ""

        OpenFileDialog1.Filter = "Excel |*.xlsx|All files (*.*)|*.*"

        If OpenFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.OK Then

            Exit Sub

        End If

        StrFileName = OpenFileDialog1.FileName

        Conn = DBConn()

 

        Cmd = New OleDbCommand("INSERT INTO claimDump([a],[b],[c],[d],[e],[f])  SELECT * INTO [Excel 12.0 xml;DATABASE=" & StrFileName & ";Extended Properties=Excel 8.0;]", Conn)

        Cmd.ExecuteNonQuery()

        ' Catch ex As Exception

        ' MsgBox("Some error")

 

        'End Try

 

    End Sub

While running the code I got "IErrorInfo.GetDescription failed with E_FAIL(0x80004005" this error.

 
Am checking my excel sheet the header name is same as per the insert statement like a,b ,c etc..

Can anyone help me on that.
0
Hi Guys,
Back again. In a bit of a steep learning curve.
At the moment I am using plain Access with no Visual Basic.
I have one query that calculates certain values, approximately 400 values in one column.
I then used DLookUp to read these values into a second query.
I then use these values to to calculate other values.
when viewing the second query it takes a very long time to display the results. (I take a walk while it is working)
I have searched Google and it seems that they do not recommend DLookUp for more than one value.
one of the ways around this is to
write a macro to make a temporary table, then update the values into a main table which is linked to the second query.
The difficulty I have is that it is not instant, it is more complicated especially when compiling the second query.
Does anyone have any advice for me.
Thanks a Lot.
Regards
Ralph
(Was taught on basic then Turbo Basic a long time ago, now must learn Visual Basic soon)
0
i am filling a form of HPPSC, when i wants to update my information. there have a problem of range validator, what i can do?
0
We're dealing with a upgrade from Office 2007 to 2016. After checking how much Access db's are in use, we found out that users made some in Access 97 and up.

What is the best approach to check compatibility with Access 2016?
Are there must have tools, or does anybody have experience with this?
0
I have an Access database that has been used for year and years

It has a form where the user embeds an Excel file.
The file then displays on the form. They can then edit the Excel file and other fields.  This worked good until a few days ago.

Both Access and Excel are version 2016, although they were XP at start.
This happened pre-update to 2016. I thought there was XP problem so updated to 2016 hoping that would work.

The problem I have is that with newly added records with the new files don't display the Excel file after moving from one record to another
Embedding the Excel file works (either an XP or 2016 Excel file) but for new records. BUT the embedded files don't display after moving to a new record (instead only a blank space)
HOWEVER the files are embedded - i checked the database table and it says  either "Microsoft Excel Worksheet" or "Microsoft Excel 97-2003 Worksheet" in the field, and the file can be edit

Old records are all good, but new ones don't display

I tried embedding a paint file to see if that worked and it was all ok

please advise
thank-you
0
a user had her computer changed to a new one. She has opened a database and there was a message that popped up but she cannot recall what the message was and she clicked on something. After that the database has opened but she cannot click on any buttons or go to any other screens. The database works fine for everyone else, she just changed something on her computer and I cannot figure out what it was. It's like she has disabled the whole system.
please help!
0
Is there anyway to start a saved "Action" in Acrobat Xi from VBA?

In acrobat 9.0  I was able to create a batch process and using send keys (not really happy using sendkeys, but it was the only way I could get this to work),  to kick off a Batch Process.

I can't seem to recreate this in Acrobat 10 and up.  Send keys no longer works as the Tools have no hot keys.

Can I use the SDK to call a previously saved action?
0
Hi Guys,

I am looking for a video or textbook tutorial that can explain step by step procedure to create a simple MS Access Project and covers all the steps from creating tables, form, reports, main page etc.

I was searching in youtube but couldn't find a complete tutorial. Any help would be appreciated.

Thanks in advance,
Sud
0
Hi All,

I use this code to open Google maps in access, it works very good it opens Google maps with the destination address,
I would like to know if its possible also to send in the from location into Google maps and it should open Google with the from and to location.    

    Dim wAdrs As String
    wAdrs = Me.Address1 & " " & Me.Address2 & " " & Me.City & " " & Me.State & " " & Me.ZipCode
    Me.DirBT.HyperlinkAddress = Switch(Nz(wAdrs, "") <> "", "http://www.google.com/maps?q=" & wAdrs, True, "")  

Thanks advance
Frimy
0
Hello,

I've been learning Access, and while I am very good with Excel, this is really difficult. I was able to make a query to show counts, but now I would like to total those rows. Here's what I have:

CountOfAPP1        CountOfAPP2      CountOfAPP3      CountOfAPP4      Expr1
80                            22                            5                            29                            2015
223                          34                            6                            47                            2016
60                            7                               2                            0                              2017

Here's what I would like to have:
Year
2015          136
2016          310
2017          69

Also, how can I make that year column show a fiscal year instead of a calendar year? It starts August 1 and runs through July 31.

Thank you in advance!
0
Hi,

I created a form (based on a query) whereby users select from a dropdown, answer to date prompt (begin/end dates) and click “run query’ button to pass those parameters to the report.  If users do not answer to a prompt, and/or do not make any selections, the report displays everything.  If users made some selections, then the report reflects selections made and only shows filtered data.  So far, this works beautifully.  

I then created another command button to exports the filtered data to excel.  Following is the code I used:

Private Sub cmdExport_Click()

DoCmd.OutputTo acOutputQuery, "qry_MyQueryName", acFormatXLSX, , True

End Sub

But, the report gives me everything (it doesn’t reflect the filtered data)

Can you please assist?

TIA

Regards,
0
Hi,

I am attempting to create a nutritional value calculator for a restaurant's recipes.  So far I have one table with the list of menu items and their ingredients, and another table with the ingredients and their individual nutritional values. How do I create a table that will list the recipe item names and their TOTAL nutritional values. Now keep in mind that the number of ingredients vary from item to item.

Thanks,

Rags
0
Dear All,
Transactions do not  work in Access 2013 at all: neither Commit, nor Rollback. No Errors, just do not work.
Help, please.
0
I have a mainform.  From the mainform I open a reportform that has buttons to open different reports based on the selected record in the mainform.  One of the reports is merely a structured report showing the selected record.  This is mainly what is showing on the mainform is then put into controls (txtboxes) on the report.  This is done by clicking a button on the reportform.  What I am running into is an error that indicates cannot find the form.  Below is one of the lines for the button.  It is the frmLocation (mainform) that is being mentioned in the error message.  Even tried  Forms!frmLocation.Form!txtLocationName and still got the same error.

Reports!rptLocationFullReport.Report!txtLocationName = Forms!frmLocation!txtLocationName

Anyone have any suggestions?

Thanks ... John
0
I have a MS Access database that I have split and imported all the tables to MYSQL successfully, I have also  linked my front-end to MYSQL back-end, but the problem is the back-end is not displaying  any of the changes that I make to the data on the front-end.
0
I have recently had to migrate an old Access Project (.ADP) to an Access .ACCDB front-end database, using a DSN file and Linked Tables to connect with a SQL Server database.
This all seems to work OK so far, but when I edit an Access Form text control box that is bound to a field in a SQL Server Table and leave it "blank", Access throws up a run-time error telling me NULLs are not permitted.
This is indeed the case - in my SQL Server Table definitions I have confirmed that NULLs are not allowed and hae a '' default, but I don't understand why an empty field in Access (or even several "space" characters) are being interpretted as NULL.
If I tab through "empty" controls in my Access Form, no problems are reported. It is only if I try to "blank out" a filled text box and leave it empty that the "NULLs" error appears.
I did not have this problem with my old Access .ADP front-end.

Can anyone tell me why Access is interpretting "blanks" as NULLs and being (presumably) rejected by the SQL Server database,
and how to work around it?

Many thanks.
0
Hello.  I have a listbox with label headers outside the listbox right above the columns.  The objective is to 'click' the label header and sort the listbox by that column.  What is actually happening is changing the rowsource.  Below is the FUNCTION that I am trying to use.  It all remains the same with the exception of the ORDER BY clause.  If I take the sql below and place it in the label click event and remove the last line below and replace it with Me.lstLocationList.Rowsource = sql and specify 'BusinessUnit' as the ORDER BY the sorting works fine.  The ORDER BY will change for each label.  The db is sql server and the tables are linked.

Function SortLocationList(orderby As String)
    Dim sql As String
   
    sql = "SELECT LocationID, LocationName, LocationCity, Jurisdiction, Country, BIACode, LocationType, BusinessUnit, IsRemoveLocation " & _
            "FROM dbo_vwLocation " & _
            "WHERE IsRemoveLocation = 0 " & _
            "ORDER BY orderby"

    SortLocationList = sql
   
End Function

I have tried several ways to call this function in the label click event but either nothing occurs or the listbox is emptied out.  No errors occur or warning messages.

What am I missing or not doing correctly?

Thanks.

.... John
0
Thank you all for your time.
I have database for housekeeping for hotel.
Main table: tbl_hkeeping_Mast
date
houkeeping_no
housekeeper_No

child table: Tbl_hkeeping_Detail in datasheet view
housekeeping_no
room_no
room_status
service

in a subform, I have room_no as combobox, which gets records from Tbl_Room_Mast. How do i exclude rooms that have already been selected in subform while entering data.
I tried rowsource property in on got focus and everywhere else. but its showing an error-syntex error or operator missing.
Then I tried it in service field after update event. It excluded first selected room in second row but that first selected room number came back in third row combobox values.
BTW roon_no is number field.

Me.HK_ROOM_NO.RowSource = "SELECT room_No from tbl_room_mast " & _
"Where room_no <> " & Me.HK_ROOM_NO & ";"

any suggestions?
I see one solution as a code loop thorough the records in the subform to create a string variable consisting of a comma delimited list of the values. But my VBA knowledge is very limited. I tried searching online but it's confusing.
thanks in advance.
0
How can i set my access program such that it can calculate the monthly contributions and demand for a new contribution after a months time.The most complex part is to calculate the penalties for defaulted payments in calculating the loan ( any defaulted payments are penlised at a rate of 10% per month).
Please help.

Thanks in advance
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.