Go Premium for a chance to win a PS4. Enter to Win

x

Microsoft Access

218K

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

For handling business card information (Entering New, Editing, Deleting) I am creating small database in Access. 2010
Thinking about tables, fields and relationships (database schema) I am wondering should I create
One or more tables.
Option with several tables:
tblClient(Client_ID,Client_FName,Client_LName,Client_Title,Client_Position,Client_Picture,Bussiness_Name)
tblAddress(Adrress_ID, Adress_1, Address_2,City, Province_State, Country,
tblContact(Contact_ID,Business _Phone_Num,Business_Fax_Num, Cell_Phone_Num ,E_mail, Web_Site)

tblClient_Address(Client_ID,Address_ID)
tblClient_Contact(Client_ID,Contact_ID)

Or should I put all fields in one table?
Is the table structure ok should I add some more fileds(Entry_Date,Last_Time_Updated…?) and what is good approach?
0
[Webinar] Cloud Security
LVL 11
[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

I have the following code in an afterupdate event of a form.  I've tried my best to construct it but am not sure it is right. Three things...

1) Will the code work?
2)  Why am I getting a "Else without If" when I try to compile?
3)  Is the after update event of the form the right place for this?

Here's my code so far:

Private Sub Form_AfterUpdate()

    Dim RS As DAO.Recordset
    Dim varitem As Variant
    
    Set RS = CurrentDb.OpenRecordset("tblSelectedLoadIDs")
    
    If DCount("[DASContractNumber]", "tblSelectedLoadIDs", "[DASContractNumber] = " & Forms!frmBillOfLadingSelector!txtDASContractNumber) > 0 Then
        With RS
        For Each varitem In listLoadIDs.ItemsSelected
        RS.Edit
        RS!SelectedLoadID = Me.listLoadIDs.Column(2)
        RS!DASContractNumber = Me.txtDASContractNumber
        RS.Update
    Else
        With RS
        For Each varitem In listLoadIDs.ItemsSelected
        RS.AddNew
        RS!DASContractNumber = Me.txtDASContractNumber
        RS!SelectedLoadID = Me.listLoadIDs.Column(2)
        RS.Update
        End With
    End If


    RS.Close
    Set RS = Nothing

End Sub

Open in new window

0
I have a checklist of Items that I use will all my clients each quarter.  I want to design an access database to keep track of the check lists after each visit.

Need some thoughts on how to set up the tables - here is what I have so far:

- my check list has SECTIONS on top - under sections are CATEGORIES - under categories are QUESTIONS (y/n or NA answers & note area)

An Example would be:  SECTION = Firewall    then CATEGORIES = Installation    then QUESTION = Firmware updated?

There are 2 parts I need help with:

1) Need tables that store the Template Check list.  Then I can have a form that I can assign a client to the template and then turn on or off parts of the template if it doesn't apply to them.  I would save the modified template so I have a modified Checklist per each client.

2) I bring up that modified check list by client and have a form to answer the questions + have a comment section - then save the checklist by date after each visit.

Not sure where to start here - any advice would be greatly appreciated.

Thanks,
0
Error received from VB when I submit my codeSnippet of the linked Excel tables I need to update in AccessError received from VB when I submit my codeGood day to all! I am literally at wit's end with attempting to create a VBA code that works to update linked Excel linked tables in Access. Let me provide some background. Attached is a screenshot of just SOME of the linked Excel tables in Access that I need to create a new field for. There are a total of 40 Groups and at 11 SELECT tables per group, you do the math that is A LOT of tables to update manually (440= 11 x 40), that is not only cumbersome, but it is painful! There has to be a more automated way!

Naturally, I would have submitted an ALTER command with ADD Column, and be done with it! But as you may know, in Access 2002, MS took away the benefit to modify linked Excel tables. So that door closed on me.

I have turned to VBA. Not that great at it, SQL is more my thing, but apparently there is literature out there to perform such task, the one I am trying to do via VBA. And as I am getting more exposed to VBA, I am getting the gist of it. I believe I created a VBA code that will do the job (see below), but when I perform the VBA, I get  Run-time error '2147217900 (8004e14):
Syntax error in ALTER TABLE statement.
Now what am I doing wrong? Can someone please help? Further, once this problem is involved, can someone please show me how to do mass ALTER TABLE statements within VBA, please? That way I can get through this painstaking task? :-( In SQL, I would have been able to do the mass statements …
0
Here is the code.
Dim PriAcct, FamAcct As String
    '
    ' Get the data
    '
    PriAcct = InputBox("ENTER THE PRIMARY ACCOUNT:", "PRIMARY ACCOUNT INPUT BOX")
    '
    ' Check to see if any data was entered
    '
    If PriAcct <> "" Then
        '
        ' Run the table updates
        '
        SQL_Update_Appt_Qry = "UPDATE ClientInfo INNER JOIN tblAppointments ON ClientInfo.ClientID = tblAppointments.ClientID_FK SET tblAppointments.DisplayName = [ClientInfo]![DisplayName], tblAppointments.PrimaryClientName_C = [ClientInfo]![PrimaryClientName_C], " & _
        " tblAppointments.DisplayNameM = [ClientInfo]![DisplayNameM], tblAppointments.DisplayNamePC = [ClientInfo]![DisplayNamePC], tblAppointments.DisplayNameFM = [ClientInfo]![DisplayNameFM], tblAppointments.MaritalStatus = [ClientInfo]![MaritalStatus], tblAppointments.FamilyMember_C = [ClientInfo]![FamilyMember_C] " & _
        " WHERE (((tblAppointments.RIGAcct)= PriAcct))"
        DoCmd.RunSQL SQL_Update_Appt_Qry

This is giving me a syntax error (missing operator)
0
I need to create a chart Y: minutes .
I have a time field. How do I convert data and use for Chart?
0
I can open access fine using the following command line but, i cannot get the syntax for it to connect in excel through any of the wizards.  Any help would be appreciated-


"C:\Program Files\Microsoft Office 15\root\office15\msaccess.exe" "\\srv05\database.mdb" /wrkgrp "\\srv05\folder2\jobboss.mdw" /user username /pwd password123
0
access 2010 vba
sql server

I have a linked that from sql server that has a field  that is stored as a string unfortunately..TARGET_GP

"Line_Color" IS STRING FIELD ALSO , BUT JUST GETTING UPDATED depending on the code, not conditional formatting

So when conditional formatting is looking at the value it does not evaluate correctly when the value is between 0.01 and 9.99
or the code below:

Dim gp As String
  

    If Len(Nz(Me.TARGET_GP, "")) = 0 Then
   Exit Sub
End If
Me!TARGET_GP = Replace([TARGET_GP], "%", "")
Me!TARGET_GP = Replace([TARGET_GP], "..", ".")
If IsNumeric(Me.TARGET_GP) = True Then
    Me.TARGET_GP.Value = Format(Me.TARGET_GP.Value / 100, "0.00%")
End If


If Me!TARGET_GP = "%" Then
Me!TARGET_GP = ""
End If


gp = Me.TARGET_GP.Value



Me!Line_Color = ""


If gp <= "14.99%" Then
  MsgBox "Sorry But the Target_Gp is Below Threshold. Please Correct.", vbCritical, "TARGET THRESHOLD"
  LINE_GP_FAILS = False
  Me.Line_Color = "RED"

End If

' red Light
If gp >= "15.00%" And gp <= "19.99%" Then
  '  MsgBox "RED dave"
    LINE_GP_FAILS = False
    Me.Line_Color = "RED"

End If

' Yellow Light
If gp >= "20.00%" And gp <= "24.99%" Then
  ' MsgBox "yELLOW"
   LINE_GP_FAILS = False
   Me.Line_Color = "YELLOW"

End If

' green light
If gp >= "25.00%" Then
 ' MsgBox "gREEN"
  LINE_GP_FAILS = False
  Me.Line_Color = "GREEN"

End If
   
   
   Exit Sub

Open in new window

0
I can get my forms to fill up the shell but the shell doesn't keep it's size.  How do I display a form so application shell doesn't appear in the background but the form size is constant?
0
I am trying to set the hide some column in a datasheet view of a form.

I read that column width and/or visible properties cannot do it.

VBA code can do it through columnhidden property.

I am using Office360.

I read also that this is can be done from the Format in GUI in old access versions.

My question:

Is that still available in access 2016?

If yes, where cause every thing is dimmed in the Format ribbon when I select the form or the field to be hidden.

Thanks.
0
Concerto Cloud for Software Providers & ISVs
LVL 5
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

I have created a stored proc to determine from a form textbox value if this value is in a table.  

I run an IF EXISTS
               
               ELSE IF EXISTS
               ELSE IF EXISTS

For some reason instead of entering information once in table, it is entering it 3 times????  Do you know what would make it do that?
0
I'm exporting an Access query to Excel and attempting to format some of the rows and columns.  

Sometimes, this code works, other times, I get the "The remote server machine does not exist or is unavailable" error message on the line indicated.  When it doesn't work, the header row is already populated with the column names.

Code is basically:
Dim rs as dao.Recordset
Dim xl as object
dim wbk as object
dim sht as object
dim rs as object

set rs = currentdb.querydefs("myQueryName").openrecordset

set xl = createobject("Excel.Application")
set wbk = xl.workbooks.Add
set sht = wbk.sheets(1)

'Write the column headers
For intLoop = 1 to rs.fields.count
     sht.cells(1, intLoop).Value = rs.fields(intloop-1).Name
Next

'Highlight the column headers and copy the header column
sht.Range("B1").Select
sht.Range(Selection, Selection.End(xlToRight)).Select     'this is the line that raises the error
set rng = Selection                                       'this is used later in the code
with rng.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.14996795556505
    .PatternTintAndShade = 0
End With

sht.Range("A2").CopyFromRecordset rs

if NOT (rs is nothing) then
    rs.close
    set rs = nothing
end if

'*******************
'whole bunch of additional formatting functions here
'*******************

if NOT (rng is Nothing) then set rng = nothing
if NOT (sht is nothing) then set sht = nothing
if NOT 

Open in new window

0
Hello All,

We have a few computers.
On one computer when i click on a control and want to open the Zoom box with Shift+F2 i get a message,
"Add-in missing or was modified". and access locks up.
I already uninstalled Access 2010 and installed it again and it did not help.
Thanks in advance
0
0
In my Access 2013 application, I am using an array to temporarily store a small amount of data I want super quick access to later.

There may be zero to many items matching the criterion to be added to the array.  

As long as some items are loaded into the array the below logic works fine.  However, when there are no items added to the array I get a subscript out of of range error when evaluating the UBound of the array.

How do I check for this possibility that there were no items added to the array?

I am trying
If IsNull(aTriggeredCostIDArray) Then but that statement doesn't evaluate to 'True' if there are no entries in the array.

In the module loading and unloading the arrays I am setting "Option Base 1".

The definition of the array:
Dim aRecipientsArray() As Long

The routine to load the array:
Private Sub loadRecipientArray()

Dim wkArrayIndex As Long
wkArrayIndex = 0

Dim rs As DAO.Recordset

Set rs = Me![frmDocumentInterestedParties_Sub].Form.RecordsetClone

If rs.RecordCount > 0 Then
'
    rs.MoveFirst
    
    While Not rs.EOF
        wkArrayIndex = wkArrayIndex + 1
        ReDim Preserve aRecipientsArray(wkArrayIndex) As Long
        aRecipientsArray(wkArrayIndex) = Nz(rs!InterestedPartyTypeID)
        rs.MoveNext
    Wend
'
End If
'
rs.Close

Set rs = Nothing

End Sub

Open in new window


The routine to unload the array:
Private Sub displayRecipientArray()
'
Dim recsRead As Long
'
if isNull(aRecipientsArray) then
      MsgBox "Array is empty)
end if
'

For recsRead = 1 To UBound(aRecipientsArray)
    Debug.Print " "
    Debug.Print recsRead, _
                aRecipientsArray(recsRead)
    '
Next recsRead
'
End Sub

Open in new window

0
I have three tables:    tCheckout                  tUsers                                                                                             tTypes data
                                       UID                             UID                                                                                                 TypeID   TypeName
                                       UName                      UName                                                                                                 1       Regular
                                       TypeID                       Regular  (1 or 0)                                                                                   2       Small
                                       Checkout_Date         Large     (1 or 0)                                                                                   3       Large
                                       Checkin_Date            Small   (1 or 0)

I have to insert a, Userid, TypeID, and checkout date into the Checkout table based on different type fields in the Users table to determine if user can update records.  I would like to write a  query to include a CASE statement to insert TypeID into the Checkout table with the use of variables.  I will be inserting this query into a stored procedure.  Can you help me with this please.
0
Hi all -- I'm taking a quick straw-poll of tools that can be easily integrated into MS Access to provide better on-the-fly graphing capabilities. I'm OK with buying a license for something but it needs to be launchable/controllable from within Access, and it needs to work really well -- e.g., produce some nice-looking graphs and pie charts. If the graphs are clickable to show details, that's a bonus. For those of you who've been down this path before, what's your go-to and why? Oh, yeah -- I'm not considering Excel as an answer, either...! :)
0
We are seeing this graphic attached to the first character of a file name in SharePoint. What do you call this? Is anyone else seeing  it? Example: new file name
0
I've inherited a rudimentary database which many issues. One of the things that is bothering me is that I am working with a CURRENT STATUS combobox lookup comprised of First Names of some of the staff who work with clients plus the value "Closed". So when there are staffing changes, the Current Status table values have to be updated and currently do not link to the employee table. I would like to populate this lookup from the Employee table but how would I do that and also include the "Closed" value in the cbobox? This is not something I've had to do before
0
Prepare for your VMware VCP6-DCV exam.
LVL 1
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

I'm was using the code below to send Outlook 2010 emails form Access 2010. We recently upgraded to Office 2016 and now I get a runtime error 287 on line  Signature = olMailItem.HTMLBody
Thanks



Error 287


 Private Sub DoEmail(Fname As String, Lname As String, Email As String, attach As String)

    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem
    Dim a As Variant
    Dim Signature As String
    Set olMailItem = olApp.CreateItem(0)
   
   
   
    With olMailItem
   .Display
    End With
    Signature = olMailItem.HTMLBody

    olMailItem.To = Email
    olMailItem.Subject = "Monthly Reports"
    olMailItem.HTMLBody = "Find attached your Monthly Cognos and/or eCAPE Monthly Reports. If you have questions please let me know." & "</br></br></br>" & Signature
   
   
    For Each a In Split(attach, ",")
        If Dir(a) <> "" Then
            olMailItem.Attachments.Add a
        End If
    Next
   
   
   olMailItem.Display
   ' olMailItem.Send

    Set olMailItem = Nothing
    Set olApp = Nothing


End Sub
0
I'm preparing a user group presentation on database normalization.  I would like to show examples of different challenging 1NF conversions.

Tackling 1NF conversions of unstructured/delimited data is a different animal that I will tackle separately.  What I would like to see from you are different repeated group column names that you've encountered.

Sequential numbering is usually what the students see.  I'd like to show them more challenging examples from your real-world experiences.  Things that come to mind are year and quarter values, week values, month name values, and day name values.  Other challenges might be values that aren't at the end of a column name, capitalization, and no delimiter between the root column name and the serialization values.
0
I have a text box on a form that calls a function in the Control Source that i want to pass the text box's name so within the function i can set backcolor, forecolor...

I don't want to have to hard code the text box's name within the call to the function.

=ELookup(??currenttextbox.name??, status)

Public Function ELookup(textboxname as ?control?, Status As String)
If Status = "x" then
   textboxname.forecolor = vbred
Else
  textboxname.forecolor = vbGreen
End Function

would i also have to pass the form name within the function?
0
I have Query A, with fields ColA, ColB, Col C and ColD, and sorted in ascending order of ColA.

I wish to build Query B with fields ColB, Col C and the LAST (ColD) that appears in Query A.

I tried to build the query using SELECT .... GROUP BY. But as soon as I add Col B and Col C as the first two columns, it messes up the original sorting of Query A and affects the result of the function LAST (ColD).
0
I am looking for a procedure where the user browse a folder to create a pdf from a selected file.I have seen examples to convert report and active sheet but that not what i am looking for.I am using ms access.
Thanks
0
I am using Outlook 2010 with Acrobat X add-on,  I am able to select a folder or individual files and export them, via the Acrobat Menu in Outlook, to an indexed PDF portfolio.  This works great.  The portfolio file contains litigation pleadings and evidence, including attachements, but only items from the source outlook folder.  I need to be able to also import photos, and other correspondence related to the case, and these need to be inserted into the portfolio based on their corresponding dates so that these extra items appear in chronological order when listed with the original message items from Outlook.   I have thought about emailing these items to myself so that they can be easily imported with the other items, but the new items have today's date on them, so the appear out of order at the bottom of the portfolio.  While outlook permits editing the subject line of sent and received emails, it does not allow me to edit the send or receive date.  Perhaps there is another add-on, or another application that exports Outlook messages to an indexed portfolio similar to Adobe's Outlook add-on; that also allows importing of external none message files.  Acrobat has another action that allows creation of a portfolio containing files and images imported from Windows File Explorer, but this is bulky and does not include an index.  I just want to be able to search an index or TOC, then click on it and bring up the document.
0

Microsoft Access

218K

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.