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 have 2 Access Forms that I am trying to pass data between.  From From my CustShipAddressList, I have an EDIT button that should open that specific CustShipAddID in a popup where I can edit the address fields.  For some reason, the button will open the pop up form but it is blank and changing my CustShipAddId number to 0 in the source table.

I have tried to 2 different types of Code.  First opens the form with a where condition and the second with openargs. Both are having the same issue.  

Thanks in advance for your assistance.  I am using Access 2016 (from Office 365 Home subscription).

Form CustShipAddressList

Private Sub EditCustShipAddBtn_Click()
   Dim CustShipAddID As Long
   Me.CustShipAddID = CustShipAddID
    DoCmd.OpenForm "CustShipAddress", WhereCondition:="[CustShipAddID]=" & Me!CustShipAddID
   'DoCmd.OpenForm "CustShipAddress", OpenArgs:=CustShipAddID
   DoCmd.Close acForm, "CustShipAddressList"
End Sub

Open in new window

DBEE---092518.accdb
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I have a database that has tables for Clients and Jobs (tblClients and tblJobs)

Currently working is a form to add a new job.
This form has a query that joins tblClients and tblJobs
It is called from a button on a menu form using…
stDocName = "frmNewJob"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec

On this form is a field cboClient.  The user selects the Client in this combo box and it populates the client details on the form.  The user then enters the new job information and saves to create the new job.
This is all working fine and has been for ages.

Now I want to do almost the same thing, but calling a copy of the above form – frmNewJobFromEditClient from the client details form.
So when the new form is opened I want it to open as a new record but with the Client details already populated (as if the user had selected the correct client).

I assumed that I would be able to put something like…
Me.cboClient = [frmEditClient].[Client ID]
in the Open event of the frmNewJobFromEditClient form.
I have tried all sorts of options of syntax without success.

Hope someone can help me out.

Regards
Richard
0
hi, I hv a sorting issues, can anyone show me how to query the results from the sample Table? currently the sorting is by Autonum.

the result i am looking for is as follow after sortingSorting-Issue.mdb:

PondName
A1
A2
A5
A10
B1
B2
B3
B4
B5
B6
B7
B8
B9
B10
B12
B13
B14
DDA1
DDA7
DDA9
DDA10
DDB1
DDC12
UK1
UK2
UK3
UK4
UK5
UK6
UK7
UK10
UK12
0
I have an access table with field ID, Item NoofHour this in sequence I like the keep summing the NoofHours
so table Order
id  Item  Hours
1    abc    5
2    ccc    10
3    yyy    2
4   kka     3

I want to make query so result are
id  Item  Hours   Totalhr
1    abc    5             5                
2    ccc    10           15                
3    yyy    2             17
4   kka     3             20

So add the above hours. I will appreciate if somebody can tell me how I can get  sum  of row above.
Thanks
0
I am working on a music database with two main tables, tblTitles and tblSongs and other lookup tables.  The main form 'frmTitles'  has a continuous subform 'subfrmSongs'.  On frmTitles I have a field "Status".  It is a lookup field (using a combo box) and the valid selections are "Active", "For Sale" and "Sold".  Now if the status is "Active" or "For Sale" I want all fields enabled except for "SoldTo", SoldDate" and SoldPrice".  On the other hand if the status is "Sold" I want only the Status, SoldTo, SoldDate and SoldPrice enabled with all of the other fields disabled.

Finally, when the form is first opened I want the status determined from the value in Status and the fields setup according to the above.  I have tried using Case statements and If - End If but I just am missing something.

Private Sub Status_AfterUpdate()
 If Me.Status = "Sold" Then
       
          ' if the user has entered Sold
           
            Me.MediaType.Enabled = False
            Me.RollType.Enabled = False
            Me.RollOrigin.Enabled = False
            Me.RecutSource.Enabled = False
            Me.TitleNumber.Enabled = False
            Me.Title.Enabled = False
            Me.GroupPerformer.Enabled = False
            Me.Genre.Enabled = False
            Me.PurchasedFrom.Enabled = False
            Me.PurchaseDate.Enabled = False
            Me.CurrentMarketValue.Enabled = False
            Me.Condition.Enabled = False
            Me.RollRating.Enabled = False
 …
0
I have designed a database application for use by several users on the network. The Access application is a client/server application with an Accde front end and Access runtime libraries installed on each user's computer. Thus not requiring a full copy of Microsoft Access.
The back end linked table database is located on a network share in a folder that all users have [Modify] permissions but not [Full Control].
The client front end file is run from C:\Program Files (x86)\Microsoft Office\Office16\ACCWIZ folder because it is a Default trusted location within Access and this location stops the security messages popping up when a user first opens the application.

However.

When the front end application is run the opening form has the following message displayed across the top:

"READ ONLY. This database has been opened read only. you can only change data in linked tables. To make design changes, save a copy of the database"
An a [Save As] button.
This would be confusing for the users.
How can I hide the message?

Thanks for your time

Neil
0
Hi Folks
I would really appreciate any comment/input on the subject
1. What is the best (free?) tool for converting .mdb back-ends to SQL Server?
2. Does it handle all (Access mdb) data-types, autonums and relationships?
3. How difficult / complicated is it to use?

thanks in advance
0
The current formula in Column E, shown below, concatenates Columns A, B, C and D to create a unique Product Number:

=A2&IF(B2<>"","-"&B2,"")&IF(C2&D2<>"","-"&C2&D2,"")

Open in new window


I need to update the current formula whereby if the specific text string "-AAU" is in Column B, it is not to be included in the final concatenated Product Number in Column E.

I have included a sample spreadsheet that has the current formula in Column E, and the updated, desired results I am looking for in Column F.

Thanks in advance for assistance with this...

Andrea
Product_Numbering.xlsx
0
Hi!  anyone have any luck with a "TELNET" like connection with MS ACCESS?    I send simple text commands to a connected unit via TELNET.    By TELNET I mean using a communications application that converses with a connected device via IP address.     Something like sending "readbuffer01" and a response like "0,00.4,2" is returned by the conneted device.   I do this over a TELNET application like Tera Term set up for IP of the connected device xxx.xxx.xxx.xxx format for IP and TCP/IP port 3602 emulating a VT100 terminal.   Any way that ACCESS can send commands and listen for a response like these terminal programs.   I am basically trying to automate data collection over the tcp/ip connection and store in a form/table.

Thanks!
Brian
0
General Access Database question - I have linked tables with column names that I refer to in Queries, Forms, and Reports.
Everything functions the way we need it to.

Is there a way of changing the Linked table column names and have the existing Queries,Forms, and Reports still function correctly?

Or, do I have to start with a new Linked table with the new column names and redo all the Queries, Forms, and Reports?

Thank you in advance for any suggestions offered.
0
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

I have a single user who just got a new computer and now can't embed MS Word documents in the OLE field of my Access Runtime application.   At first MS Office wasn't licensed, but we took care of that and still have the problem.  I disabled click-to-run, turned off User Account Control, went into the Trust Center of MS Word and enabled All controls related to the ActiveX Settings, I also turned off Safe mode.  I disabled all but the basic Add-Ins that are working fine on my system.  We are going to test disabling Anti-virus tomorrow morning, but wanted to see if anyone could think of anything else that might be causing this effect.  I can successfully embed other document types, like MS Excel Worksheets, WordPad document, but MS Word isn't allowing the Insert Object mechanism to embed a .doc or .docx file.


When Windows makes a shortcut Word icon on his start menu, it adds those two little arrows, like a compressed folder, not sure if that's related.  Before Office was licensed, he had two little gears instead.  He is using Windows 10 Pro, MS Office 2013 without the full MS Access and click-to-run was a background process, but killing that didn't help either.
0
Parameter Query
Please see Embed FIle for snapshot of a Parameter Access Query. I have a form that supplies the input to this query.
All the fields on the Form ARE NOT required to be filled in except the Date Range. The other fields on the form are
Filtered fields. If the user Does NOT select any filter field but just selects the Date Range, the design of the Parameter Query works.
If the user selects the Date Range and filters any of the other fields, the Parameter Query does not work correctly.
Is the parameter query not the best way to return the correct information based on what the user does on the form?

I am not a programmer. Is there a better way to do this?
0
I have a column that has values like this "29MAY2018" i.;e of "ddMMMyyyy" format. How can I convert this to date type and find difference of two dates? Please kindly give me code to do this in Access Query
0
Following code is for deleting one sheet on the workbook. I need to delete multiple sheets from one workbook. Can anyone amend below code tol delete multiple sheets(with sheet name).

Public Function WorkSheetDelete_YE_FocusSites()
    On Error GoTo WorkSheetDeleteErr

    Dim mySheet As Object
    Dim xlApp As Object
    Dim wrkb As Object
    Dim strName As String

    strName = "C:\kk\kkk.xlsx"
    Set xlApp = CreateObject("Excel.Application")
    Set wrkb = xlApp.Workbooks.Open(strName)
    xlApp.DisplayAlerts = False
    For Each sh In wrkb.Worksheets
        If sh.Name = "k1" Then
        wrkb.Worksheets(sh.Name).Select
        xlApp.ActiveSheet.Delete
        End If
    Next
    wrkb.Save
    wrkb.Close
    Set wrkb = Nothing
    Set xlApp = Nothing

WorkSheetDeleteErrExit:
    Exit Function

WorkSheetDeleteErr:
    MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
    Resume WorkSheetDeleteErrExit
End Function

Thank you
0
Hi

In Access VBA I use the following code to get the path  that the project is in. It works on my client's computer but not on mine.
Why might this happen?

Function Get_DropBox_on_Desktop()

     Dim oDataBasePath As String
     oDataBasePath = CurrentProject.Path
     Dim arrSplit As Variant
     arrSplit = Split(oDataBasePath, "\")
     Dim oDatabaseFileName As String
     oDatabaseFileName = arrSplit(UBound(arrSplit))
     Get_DropBox_on_Desktop = Replace(oDataBasePath, oDatabaseFileName, "")

    
End Function

Open in new window

0
I'm using the below code to delete outlook items wit xlsx attachments. The issue encountered is that only some of the emails are deleted. Any thoughts

On Error GoTo SaveAttachmentsToFolder_err

    Dim ns As NameSpace
    Dim Inbox As Outlook.MAPIFolder
    Dim Item As Object
    Dim Atmt As Outlook.Attachment
    Dim FileName As String
    
    Dim SubFolder As MAPIFolder
    
    
    
    
    Set ns = GetNamespace("MAPI")

    Set recip = ns.CreateRecipient("OMHA.HQ.MI.REPORTS")
   
    Set Inbox = ns.GetSharedDefaultFolder(recip, olFolderInbox)
    
    Set SubFolder = Inbox.Folders("MATS")


    If SubFolder.Items.Count = 0 Then
    
        MsgBox "There are no messages with attachments in the MATS folder.", vbInformation, _
                "Nothing Found"
        Exit Sub
    End If

    
    
    
    'Delete Outlook items
    For Each Item In SubFolder.Items
    For Each Atmt In Item.attachments
    If Atmt.Type = 1 And InStr(Atmt, "xlsx") > 0 Then
    Item.Delete
    End If
    Next Atmt
    Next Item
   
    
    
  
    
    ' Clear memory
SaveAttachmentsToFolder_exit:
    Set Atmt = Nothing
    Set Item = Nothing
    Set ns = Nothing
    Exit Sub
' Handle Errors
SaveAttachmentsToFolder_err:
    MsgBox "An unexpected error has occurred." _
        & vbCrLf & "Please note and report the following information." _
        & vbCrLf & "Macro Name: GetAttachments" _
        & vbCrLf & "Error Number: " & Err.Number _
        & vbCrLf & "Error Description: " & 

Open in new window

1
I'm trying to create a query in which I'm referring to a text field in a form, but I'm getting an error:

Microsoft Access database engine does not recognize 'forms!Stats!txtFromDT' as a valid field name or expression

Why is this happening?

SELECT *
FROM tblEmployee
WHERE [Date] between forms!frmStats!txtFromDT and forms!frmStats!txtThruDT and [WorkerID] = forms!frmStats!lstWorkID;

Open in new window

0
Failure on SQL Statement:  Too Few Parameters expected 1  

Dim strSQL As String
strSQL = "UPDATE UserActivityLog SET UserActivityLog.Activity2 = ""LogOff"" AND UserActivityLog.LogOffTimeStamp=Now()" & vbCrLf & _
"WHERE (((UserActivityLog.Activity2) Is Null) AND ((UserActivityLog.UserName)=[Forms]![frmLogin]![Name1]))"


CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
Hello Again Experts.

I must apologize in advance for my brevity, but I recently injured my hand in the wood shop, and typing is somewhat difficult.

The situation that I am dealing with right now is that I am very frequently experiencing conflict between changes that I make in my Access tables/queries, and keeping my VB.NET Windows Forms app in sync with these changes. For example, if my program is stable with no DB issues, and I decide that I want to add a new field/column to a given table using Access, when I return to my program, it invariably produces errors related to the changes I make.

Today I made a change to an Access Query which is attached to a DataGridView in which I connected a new table to get a related field value. The Query works fine in Access, but now not only does the program fail to compile because of the DGV association, but the Data Source Configuration Wizard shows what before was a View is now a broken Table!

I'm pretty sure that Visual Studio stores a boatload of data pertaining to my database, and that needs to agree with the MDB file, so I just need to know that correct way to make minor changes to my tables and queries. Ultimately what I'm hoping to accomplish is to find a way to have ONE source for my database definitions, and just have a file (or files) store the data.

I realize this is not a lot to go on, but that's all I can offer for the moment.

Many Thanks,
Tony G.Example Error Condition
0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Hi Experts,
How do I format UserName from Last, First to First Last in Coding not in Query?

for example:  Doe, Jane
I have a text box name Text74
Text74 = UserName (Doe, Jane) but I want to be Text74 = UserName (Jane Doe) shows on the report footer.

Thanks,
0
Hello, need to add a Date/Time in the following statement:

CurrentDb.Execute "Insert into UserActivityLog (UserName, Activity, ComputerName) Values ('" & TempVars("UserName").Value & "','" & Activity & "','" & Form_frmLogin.CompName & "')"


to:  I added the field:  TimeStamp (data type is Date/Time but don't can't get the Values part right....

CurrentDb.Execute "Insert into UserActivityLog (TimeStamp, UserName, Activity, ComputerName) Values ('" & TempVars("UserName").Value & "','" & Activity & "','" & Form_frmLogin.CompName & "')"
0
I'm trying to figure out how to use a command button onclick event to spell check an entire Access tabbed form.  Does anyone have a solution?

I have tried this code but it gives me an error that reads:

"Databasename can't move the focus to the control txtExpirationDate"  The field is set to TabStop = No but I tried TabStop = Yes also and got the same message.

Private Sub cmdSpellCheck_Click()

    Dim i As Integer
    'Turning off warnings to prevent completion message after each control
    ' is checked
    DoCmd.SetWarnings False
    'Loop through each control on the form
    For i = 0 To Me.Count - 1
    'Check to see if the control is a textbox
    If TypeOf Me(i) Is TextBox Then
    'Verify that focus is set to the control
    Me(i).SetFocus
    'Select starting point and select the contents of the control
    Me(i).SelStart = 0
    'Verify that the control contains data and select the contents
    If Len(Me(i)) > 0 Then
    Me(i).SelLength = Len(Me(i))
    'Run the Spell Checker on the contents of the control
    RunCommand acCmdSpelling
    End If
    End If
    Next i
    'Tell user that the check is complete for this record.
    MsgBox "Spell Check is Complete"
    'Turning warnings back on
    DoCmd.SetWarnings True

End Sub

Open in new window

0
Does anyone know if its possible for me to set up an email template to include several PDF attachments eg Employee Handbook and polices.
So I can then automatically send to each new employee?
0
I have a table PaxFichas and a second table CODES. In Paxfichas I have columns such as Gender, CustStatus etc which all refer to Codes for getting the correct description.  I thought it would be possible to design 1 single query to get all the relevant descriptions such as

SELECT PaxFichas.*,  Codes.Txt1 AS GuestStatus, Codes_1.Txt1 AS Gender
FROM (PaxFichas LEFT OUTER JOIN Codes ON PaxFichas.CustStatus = Codes.CODE) LEFT OUTER JOIN Codes AS Codes_1 ON PaxFichas.Sex = Codes_1.CODE
WHERE (((Codes.ID)=5) AND ((Codes_1.ID)=24));

I have 6 records in PaxFichas and the references to CODES must be valid entries. However, this query will return more than the expected 6 records from PaxFichas (actually it returns 14 records - many of them duplicates). Do I have to build several interim queries or is it possible to handle in one singe query? Thank you so much for any help!
0
I have an Access 2016 ADP (Project) as a front-end to a SQL Server 2008 R2 Express database.
This arrangement has been running OK for the past 8 years.

What I am currently trying to do seems relatively simple, but I am having trouble achieving it.

In Access Form-A,  bound to Table-A, I have a combo box that fills a field with a value from Table-B.
If an entered value in the combo box is does not currently exist in Table-B, I use the "NotInList" event to add the new value to Table-B then immediately open Form-B (bound to Table-B) so that a couple of other pieces of data can be saved for the new record. This works OK up to a point.

Form-B is a continuous Access Form and I would like to immediately jump to the newly entered record in the list of all records so that the operator doesn't need to go scrolling down the list to find the new record.
How do I achieve this?

I have been trying to use "bookmarks" from recordset clones without success. Other on-line sites suggest using the "RecordsetClone.FindFirst" method, but my ADODB.Recordset clones don't seem to offer the "FindFirst" property.

Any suggestions gratefully received. Thanks.
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.