Improve company productivity with a Business Account.Sign Up

x

Microsoft Access

219K

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've been searching all over the web for help with this but I'm not getting exactly what I want. I have an application that uses an access database. The data shows in DataGridView. Its displaying the data just fine in the datagridview but when I make changes in the DataGridView, its not saving. Please review my code and tell me what I'm doing wrong?


Imports System.Data.OleDb
Module project
    
    Public con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= E:\diploma\project\Translation patent Application\Data_Base\Translation patent.accdb")

   
    '=============================================
    Public bookDT As New DataTable
    Public bookDA As New OleDbDataAdapter
    '==============================================
    Public Sub books_load()
        bookDT.Clear()
        bookDA = New OleDbDataAdapter("select * from Books", con)
    bookDA.FillSchema(bookDT, SchemaType.Source)
        bookDA.Fill(bookDT)
    End Sub

End Module


Imports System.Data.OleDb
Public Class Form_Books


    Private Sub Form_Books_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        books_load()
        DataGridView1.DataSource = bookDT

    End Sub

    Private Sub save_Click(sender As Object, e As EventArgs) Handles save.Click
        
        
        '======================================================
        bookDT.Rows.Add()
        Dim last As Integer = bookDT.Rows.Count - 1
        bookDT.Rows(last).Item("BookID") = BookID.Text
   

Open in new window

0
Free Tool: ZipGrep
LVL 12
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

For some reason my SQL in VBA is showing blank in my forms box. The SQL works fine in a query. I have several other forms like this one and they all work fine but not this SQL for some reason. What's wrong with it?

Thanks for the help.
If Me!txtShowAllSourceJobs = True Then
'This will show all the jobs finshed or not.

 sSQL = "SELECT SourceSku.SourceSkuID, SourceSku.CustFirstNm, SourceSku.CustLastNm, SourceSku.CustBusNm, SKUs.SKU, Left([SkuNM],48) AS SkuName, SourceSku.dtmAdd, SourceSku.SourceJobComplete, SourceSkuStatus.Status, SourceSku.dtmEdit" _
& " FROM SKUs INNER JOIN (SourceSku LEFT JOIN SourceSkuStatus ON SourceSku.SourceSkuStatusID = SourceSkuStatus.SourceSkuStatusID) ON SKUs.SkuID = SourceSku.SkuID" _
& " WHERE (((SourceSku.SourceSkuID) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustFirstNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustLastNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustBusNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SKUs.SKU) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((Left([SkuNm], 48)) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.dtmAdd) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.SourceJobComplete) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or 

Open in new window

0
I am converting the Access system into ColdFusion  web System . I Almost completed the functionality in the my web system . But there is some hidden action performed in access system and i am Unable to understand following code that is running in the Access system on the button clicked.I just need the query that is running behind this following code. where from i can find the object or that database query from the Access system that is used in the following below code. access system is build in 2013 .

Please have look on the following below code and please share with you understanding any type of help regarding to this trouble is  appreciated  heartily . Thank in advanc



Private Sub GenerateWOBtn_Click()


'Generate a WO Request to Production

Dim RstOrderDetail As New ADODB.Recordset
Dim strSQLOrderDetail As String


    strSQLOrderDetail = "OrderDetail where OrderID = " & Me.OrderID
    RstOrderDetail.Open strSQLOrderDetail, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
   
    Do Until RstOrderDetail.EOF
        If RstOrderDetail!FormulaInfoID <> 0 Then
            RstOrderDetail!WORequest = 1
        End If
        RstOrderDetail.Update
        RstOrderDetail.MoveNext
    Loop
    RstOrderDetail.Close
   
    Me.Requested = 1
   
   
        If IsLoaded("OrderFrm_Main") Then
            Forms![OrderFrm_Main]![OrderFrm_Detail].Form.Requery
        End If
   
   
   
End Sub
0
I have some VBA code that checks if a control is blank and if so asks the user if it is intentional, it adds a notations to a text box and saves the data, if no then it returns the user to the control. That all works fine, however if the control is Not blank the data is not saved, nothing happens, I believe that is due to my If statement that checks for the empty control does nit know what to do if the condition is not true. I think i could fix it by adding an Else but wouldn't I have to copy all that code that inserts the data or is there an easier way to handle the 2 conditions?
 
Private Sub btnNext_Click()
'Single Item Save Button
Dim strSql As String
Dim MsgAns As Integer
On Error GoTo Error_Procedure

bSaveClicked = True

If Len(Me.cboSubcat & "") = 0 Then
MsgAns = MsgBox("You Did Not Select a Subcategory for " & Me.ITEMNMBR & vbCrLf & " Click YES if this was intentional and" & vbCrLf & "Text will be added to the Notes field Automaticaly and Saved.", vbYesNo + vbExclamation + vbDefaultButton2, "Blank Subcategory")
If MsgAns = vbYes Then
Me.cboAddSub = "Yes"
Me.txtComm = "The Subcategory has been left blank for this Item Number"
'Checks item_cat_subcat table aginst the form Me.txtItem control for a record if none it inserts it
 If DCount("ITEMNMBR", "item_cat_subcat", "(ITEMNMBR = '" & Me.txtItem & "')") = 0 Then
       strSql = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,Comments,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & 

Open in new window

0
On my MS Access  Data Entry form I have two optional group controls.
First is opt_Category with next values:
1 – All
2 – Active
3 - Not Active
4 – In Process
5 – Completed

Second optional group control is opt_City
1- Paris
2- London
3 -Toronto
4- Amsterdam

On Main form I have sub forms (tabs) for each of those City where I enter some values and some dates
Aplication submited Date:
Aplication Process start Date:
Aplication Decision Date:
Notified Date:
In lower part of form I have detail sub form where I display results of proper selection combination.
Let say I want to show all applicants regardless of  Category or City or dates  entered in.
 I will show  applicants from Category and all of Cities regardless if date entry exist or not exist (as business rule allows to enter – register an applicant without any of dates present in).
If I want to show applicants from Paris and London that have submitted application (Submitted Date Is Not Null) or (Submitted Date is Null but Process Start Date Is Not Null) Business rule allow this kind of entry.
And so on…
What I want to say if I am creating separate query for each of those combination it will take a lot time and every new City added will multiply number of queries .
For now I have four Cities and number of Combination is 32 If I add next city it will go to 64.
How to create this selection criteria on fly? I assume It has to be “where” clause?
And I assume I need to loop through those two option …
0
This is the code were I am getting errorThis is the code were I am getting errorI am having a problem with SQL query I am trying to save a file into access database but getting  Run time error '- 2147217900(80040e14)': Syntax error (missing operator) in query expression I am getting this error.
0
I'm in the process of converting a MS Access 2K compiled application that was developed to run on XP so that it will also run on Win 10 computers.
Our goal is to get the application to run on both platforms so we do not have to replace all of our machines.

 (We understand the risks in using XP machines and have taken steps to mitigate them, so please help me solve this problem rather than tell me why I should not use XP).

The problem is that when XP connects to a networked printer then printer name assigned is "printername on servername": for example, "Shipping on FileServerPro".
When I generate a report in MS Acesss I have the option of selecting the default printer or a specific printer.  Each employee has a printer near his or her desk that is set up as the default printer.  Reports that need to print to the shipping print are hardcoded to print on "Shipping on FileServerPro".

Windows 10 uses a different naming convention for network printers, causing the printer name to appear simply as "Shipping" in the Printers and Faxes section.

As a result, when the MS Access code is executed in attempt to print to the shipping printer, we get an error message which essentially states that Access can not find "Shipping on FileServerPro" when run on Win 10 computers (of course this is because the Win10 computer knows the printer simply as Shipping).

I can make changes to the XP machines, or the Win10 machines, or to Access, but I need the same code to print to my …
0
I need help with an append query that has a key violation but I don't understand why. The destination table has an autonumber primary key, that is the only key, and I'm not trying to append anything into that field.

I assumed Access would generate an autonumber when I appended the rest of the fields but I don't think that's the case.  I am using MS Access 2016.
0
After installing Access 2003 on a new Windows 10 PC, the object names in list view are truncated with .., even if the name length is less than the width available.  This occurs regardless of the total length, or whether or not we refresh the database window, change to/from other views, switch between objects.  But it doesn't happen in Win7 or earlier; just Win10.

It's clearly not a major issue, and is also taking place with 16-year-old software.  But that said, we're stuck with 2002 for some projects, and it's quite frustrating when several objects have nearly the same name, with just a suffix differentiating them.  Switching to detail view shows the whole name, but is much less convenient.

Any ideas?

msAccess list view truncating
0
Good day everyone,

I need to fetch a data from a database into my VBA project.
The code runs for a couple of minute because it connects to a remote server.
I am trying to implement an interactive dialogue to show the progress and need support on how to best implement this feature...

Below is my code snippet

Dim sTims, stTime, endTime As Double
Dim startTime As Boolean

Sub CommandButton1_click()

ProgressBar1.Visible = True

sTims = Timer

startTime = False

If CheckBox1.Value = False And CheckBox2.Value = False Then MsgBox "Pls Select a Location to View", vbOKOnly, "Oops!": Exit Sub

ShpData.closeRS
ShpData.OpenDB

StrSQL = "SELECT [Shipment Number], [WHExecution Date], [WH Officer],[Driver Name],[Quantity]FROM [ShipmentRecords] order by [WHExecution Date] DESC"

ShpData.DataRec.Open StrSQL, ShpData.DataConn, adOpenKeyset, adLockOptimistic

    If ShpData.DataRec.RecordCount > 0 Then
    
    Range("E12:I12").Select
    
    Range(Selection, Selection.End(xlDown)).ClearContents
    
    Range("E12").CopyFromRecordset ShpData.DataRec
    
    Else
        MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
        Exit Sub
    End If
    
    cleanup
    endTime = Timer
    startTime = True
    ProgressBar1.Visible = False
End Sub

Sub cleanup()

ShpData.DataRec.Close
ShpData.DataConn.Close
 
    Set ShpData.DataConn = Nothing
    Set ShpData.DataRec = Nothing

End Sub

Sub timerbar()

Static intCount As Integer

Open in new window

0
Get 10% Off Your First Squarespace Website
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

I have a access table that was created from an import of a Excel spreadsheet.  All the Dates for the part are represented as column names.  I need to essentially "UNPIVOT" this table and put the column names as rows.  The desired output would look like this
Item number |     Usage Date     | Qty
      010019     |   August 2017     |  34
      010019     |September 2017 |  10
      010019     |October 2017      |  57

No need to include the zero quantities so the result table will be simpler.
Attached is a copy of the database with this table.  Keep in mind that the way to UNPIVOT should be dynamic.  Each month we import a new spreadsheet and the column names will be different, so we need to UNPIVOT after each import of the spreadsheet.
0
hi everyone i am doing a website using asp.net and i want to display my access database table content in a formview but the problem is this, i am using a master page and div tags, i devide my form in 3 div tags, the side bar, and menu then main content div, i floated my content div to the right but now i cant see my formview on the content div
0
Using Access 2010 I want to write VBA that will allow me to:
1. Rename a .JPG file.
2. Read meta data from a .JPG file. (e.g. Title)
3. Write or update meta data in a .JPG file. (e.g. Comments)
Assume path such as "C:\images\example.jpg"
I am looking for what commands to use or pointers to helpful code.
Thanks.
0
sorry I'm new to ms access
anyone can help me to perfect these function??

If Format(Now(), "yy") = x Then
DoCmd.OpenForm MeRecord + x
End If

example : current year = 2018,it reads the 18,and open form MeRecord18.
example : current year = 2019,it reads the 19,and open form MeRecord19.
0
Hi folks,

I'm swapping excel to access, however, I'm facing some problems.

 In excel I used to use the following formulas for conditional formating:

1 -  " =$a1<>$a2 " then put a line border.

2 - " =a$1='sales order' " then highlight the whole row.

Does anyone know how to do the same in access?

Thanks
0
I have a combobox on a form.  The combobox allows the user to select from a list of options.  But is there a way to allow the user to enter a string of characters in the combobox that are not in the list as a one-time entry only?  It other words I want it to be allowed but not entered in the combobox rowsource table.
0
Hello, I would like to use Visio as a "sales automation tool". I will be reviewing floor plans with customers and want to create an interactive experience. The plans (which I already have) contain plotted assets that are currently stagnant and not connected to any data source... I would need each asset to have data linked to it, I assume via the serial number of the asset/SmartShapes. The cost of each item in the drawing would be retrieved from either Excel or an Access database and the TOTAL COST OF OWNERSHIP would appear on the drawing page in a table summed up.. As I "uncheck" assets, the total cost of ownership would update accordingly, providing the customer with updated costs should they remove specific devices.. The data part I can customize and insert..

...but... would anyone be interested in helping me with a high-level overview of what else I need to do? I have limited experience with Visio but am able to pick up any software rather quickly. Would this require more than Visio/Excel?
0
In ms access 2007.  Report graph not displaying orange on graph... showing brown... associated table showing correct color when set using. Me.cht.seriescollection (I).points(x).color=RGB(255,163,0).  All other colors appear correctly and chart corresponds to table cell background).d. Tried various variations of orange and chart shows pink, light green, etc.  Tried to modify pallet for chart (standard and chart fills) to see if it would try to match... no luck.  Original pallet did not have orange... when reset pallet showed orange... but no change.... any idea what is going on and/or how to fix it?  The orange color is a statutory requirement.
0
I am trying to save attachments from excel VBA to access VBA. I want to create a button in Excel VBA and when you click the button brower should open and select a file then after selecting the file should get saved in Access as an attachment.
0
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi,

I have this code
    'radio button medical devices clicked and mda reg no not inserted validation
    If Me.frameproducttype.Value = 1 And (Len(Trim(Me.txtmdaregno.Value & "")) And Len(Trim(Me.labelmda.Value & ""))) = 0 Then
    SaveProduct = False
    txtmdaregno.SetFocus
    Exit Sub
    End If

Open in new window


Private Sub labelmda_AfterUpdate()

If Me.labelmda.Column(0) = "1" Then
    Me.txtmdaregno.InputMask = ">0000000000\A"
    ElseIf Me.labelmda.Column(0) >= "5" Then
        Me.txtmdaregno.InputMask = ">0000000000"
        Else: Me.txtmdaregno.InputMask = ">00000000000"
End If

End Sub

Open in new window


In isolation, these codes work as intended.

However, when I put the input mask and the validation code together, it fails the validation rule by stating that these three conditions i.e.
If Me.frameproducttype.Value = 1 And (Len(Trim(Me.txtmdaregno.Value & "")) And Len(Trim(Me.labelmda.Value & "")))

Open in new window

are 0.

Basically, I have a radio button,(Me.frameproducttype.Value) a listbox (Me.labelmda.Value) and a text box (Me.txtmdaregno.Value). After selecting the radio button, the user selects one item on the  listbox and then keys in items on the text box based on the inputmask of the listbox.

Could anyone help. I have been stuck on this for a long time.

Thanks
0
Using Access 2016 - Microsoft 365 Enterprise E3

Receiving runtime error 3035 (see attached) on a database - it runs fine on my desktop but has an error on laptop.

Checked avaialable disk space and that should not be an issue

Added this code and I still get the error

Private Sub Form_Load()
    DAO.DBEngine.SetOption dbMaxLocksPerFile, 1000000
End Sub

Any suggestions how I can trouble shoot - is this possibly a RAM issue?

Thanks
Error-Message.JPG
0
I have a table (tblPPData) with the following fields

PPID - Unique Number format
PPParticipant - String format
PPSurveyNumber - Number format



I have assigned a string variable for the prefix of the fields "PP"

Prefix10="PP"

Using the Prefix10 string variable

Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "Participant= '" & [Participant] & "'")   The result is correct
Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "SurveyNumber = 1")                         The result is correct



What I would like to do is use the Prefix10 string variable using both the Participant and SurveyNumber fields as criteria


The following statement works correctly until I try to use the Prefix10 String variable as I did above for the individual criteria lookups.

Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", PPParticipant= '" & [Participant] & "'" & "And PPSurveyNumber=1") The results are correct

This statement works as well
Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & [Participant] & "'" & "And PPSurveyNumber = 1")

As soon as I start trying to use the Prefix10 string variable in place of the prefix for the PPSurveyNumber (Prefix10 & SurveyNumber"=1") I start having issues.


This statement gives me a Run-time error 13 - Type mismatch
Debug.Print DLookup(Prefix10 & "ID", "tbl" & Prefix10 & "Data", Prefix10 & "Participant = '" & [Paticipant] & "'" And …
1
Reference a MS Access Form.PopUp = Yes|No

Is there a way to change this via vba programmatically in a compiled (accde) version?
0
Is it possible using Access VBA to convert a printable character to a hidden character?  After I load a spreadsheet into an Access table I need to convert "{#}" to CRLF, convert "{^}" to TAB in some columns.  Does someone have a piece of VBA?  tia, Marc
0
My OS is win 10 pro 64 bit and I have Office 365 and I use Access.  I am researching to see how relational databases work, especially having a main form and sub forms.  Are there some samples that I could check it out.  thank u.
0

Microsoft Access

219K

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.