Microsoft Access

216K

Solutions

178

Articles & Videos

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 know there are exceptions to normalizing a table structure but I'm not sure this is one of them.

I need to track the particular days of the week in a date range in order to later use them in a count.

For example, I have a date range and need to find the count as follows:

DateStart: 12/15/2016
DateEnd: 3/25/2017

Count: How many total number of days are in each month of the date range if we only count Tuesdays and Sundays.

I have a table, tblAssign, that holds the DateStart and DateEnd fields.  I also have a table, tblDates , with entries for each day date, i.e. 12/15/2016, 12/16/2016, 12/17/2016, etc.

My question is:
Should I
(1) add fields to those two existing tables to include the days of the week?  For example, I would add a boolean yes/no field for each day of the week to the table, tblAssign.  I would also add a DayOfWeek field to the table, tblDates, specifying which day of the week each date falls on.
OR
(2) Add a new table, tblDaysOfWeek then add an intermediary table between tblAssign and tblDaysOfWeek which contains IDs between the two.  Also add a 3rd table between tblDaysOfWeek and tblDates, again containing IDs between the two tables.

Option 2 seems normalized to me, while option one repeats a whole lot of information.  However, is it really necessary to introduce so many new tables?  I'm not seeing how #2 is going to simplify anything in the long run.

What's the right thing to do here?
0
Revamp Your Training Process
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Hi

I am a newby, however I am progressing with a treeview project. My latest problem is;

3061 Too few parameters. Expected 1

This comes up after stepping through the following;

    Dim rsReqs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
   
   
    strSQL = "SELECT dbo_BomHeaders.ID, dbo_BomHeaders.BomReference, dbo_BomHeaders.Description " _
    & "FROM dbo_BomHeaders " _
    & "WHERE (((dbo_BomHeaders.ID)=[Forms]![frm_Treeview_Example]![Combo0])); "
   
    'Debug.Print strSQL

    Set db = CurrentDb
    Set rsReqs = db.OpenRecordset(strSQL)   (3061 appears at this point)

The SQL string is copied from the query (criteria) which is the record source for the form on which the combo box resides. I have also implemented Alan Brownes 'Return to the same record next time form is opened' so that the combo box is populated on the form opening.
0
So I have a form and a subform I would like to delete a record on the subform.  I have tried it many ways and I can't get the record to say deleted.  

I have highlighted the record and hit the x on the toolbar.  It says you wont be able to undo this but they are wrong.  When I click on refresh all the record returns.

I have clicked on the record and hit delete on my key board and it says I wont be able to undo and once again wrong.  When I click on refresh or get out of the form and back in the record is still there.

Last but not least I created a delete record macro using the wizard on the forms tool bar.  

Which created a macro that says
On Error
go to next
Macro Name

gotocontrol
Control Name = [screen].previouscontrol].[name]
ClearMacroError

if not [form].[newrecord] and not [form].[dirty] then
runmenucommand
command deleteRecord

end if

if [form].[newrecord] and not [form].[dirty] then
beep
End if

if [form].[newrecord] and not [form].[dirty] then
runmenucommand
command undorecord
end if

if [MacroError]<>0 then
Message box
message = [Macroerror].[Description]
Beep yes
type none
title
end if

This also does not delete the record it removes it but upon refresh it is back.   Help!
0
I am trying to use this to make navigation button not visible on a sub-form but I get "Object required"

Forms!frmMainForm.Form!frmSubForm.Form.NavigationButtons.Visible = False
0
I have a report that calculates ship totals for this year, last year and two years ago. I need to make another field that shows ship totals for the previous 4 months. So if today is June I need to  total  Feb - March - April - May. If this month was March I would need to  total Nov - Dec - Jan - Feb. I attached a portion of my sql if that helps.
0
I'm trying to format the entry of a phone number in a form field without using an input mask.  If the user enters:

111111111 the result needs to be (111) 111-1111
or
(111) 111-1111 the result needs to be (111) 111-1111


I've tried this but it isn't even close:

Private Sub txtPhoneNumber1_AfterUpdate()

    Me.txtPhoneNumber1 = Replace([PhoneNumber1], "(", "")
    Me.txtPhoneNumber1 = Replace([PhoneNumber1], ")", "")
    Me.txtPhoneNumber1 = Replace([PhoneNumber1], "-", "")
    Me.txtPhoneNumber1 = Replace("Me.txtPhoneNumber1", " ", "")

    Me.txtPhoneNumber1 = "(" & Left(PhoneNumber1, 3) & ") " & Mid(PhoneNumber1, 4, 3) & "-" & Mid(PhoneNumber1, 7, 4)

End Sub

Open in new window

0
I am currently putting together a new Access 2016 Database. I presently have a main form which uses names as the main table. In a subform, I have an appointment form which is comprised of linked tables. In this case the Appt form has a table that includes project, time and date information that is linked to a table that included address and contact information. These forms are connected as a one to many relationship with contacts being the one and appt time/date/project being the many (One contact can be associated with many appts). The main form is connected to the subform with a one to many relationship, the name being the one and the appts being the many.

The Tables and Fields are as follows:

Main Form
Control Source - Table:
MainNameDatabase
Fields:
MainNamePrimaryID
FirstName
LastName

Subform
Control Sources - Tables:
TalentApptDatabase
Fields:
ProjectTitle
ApptDate
ApptTime

ContactDetailsDatabase
Fields:
FirstName
LastName
Company
AddressLine1
AddressLine2
City
State
Zip
(These tables' fields are combined into one subform related the to the main form)

My question is this, I want to be able to type into one of fields in ContactDetailsTable and see if that record already exists and if so associate the entire rocord with the appt record.  If not, then I want to add a new record to the ContactDetailsDatabase. What is the best way to do this, can you?

IN other words, is there a way to bring up a combo box in say Company that will …
0
I have an MS ACCESS report.   in the detail section I count the number of objects  for each day, then have a percentage calculated.

In the footer, I SUM the counts for a grand total  of the selected dates.   My problem is, I dont know how to write the grand total percentage.

In the detail section, I write my percentage calculation like this and it works:  =[CountOfChartedStatus2]/[CountOfTOTAL1]

In the footer, these are the two fields I need to make a percentage of:    =Sum([CountOfChartedStatus2])   and   =Sum([CountOfTOTAL1])

How should I write this to get a percentage.   Thanks.
0
I have a tabbed form.  Each tab is a sub-form using a different record source.  I can't figure out how to tab to the first field on the text tab from the last field on the current tab
0
I have a form that I’ve been using to enter data into a table (let’s call them “Form2016” and “Table2016.”) I need a 2017 version of both so I copied them and renamed the copies “Form2017” and Table2016.” I made the new form point to the new table. I did not delete any of the previous data from the new “Table2017.” I will just add additional data to that table.
     Here’s the weird part: “Form2017” already had 165 entries. I added two test entries and closed the form. When I re-opened it, those two entries are now the first two entries, not the last two. And in “Table2017,” they are also the first two entries instead of being the last two entries. Why is that?
0
Back Up Your Microsoft Windows Server®
LVL 4
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Is there a list out there which describes what all the Access FORM properties do?
0
Hi Experts,

I realized that MSFT changed the way it was handling combo boxes that were not selected any values, up till version 2007 the it was considered null, and the function IsNull(myComboBox) returned true, while IsEmpty(myComboBox) returned false, and from version 2010 its the opposite, Isnull is false and IsEmpty is true.

Now I'm used to have nz(MyComboBox,FillInValue) working fine and now that does return the FillInValue anymore, I guess its due to the above.

Now my question.
Did you also experienced that behavior?
What is the alternative to the NZ function? (Besides for having to use the IIF() function which will make things slower IMO).

Thanks in advance.
0
I'm trying to import tables from my Access database, I was actually successful at importing one table, but since haven't been able to import anything. I'm using the same data source and I can see the tables but they won't import to the MySQL database I selected.
0
Hello,
I have a MS access process that reads a MS access table and creates another table. In the original table there is a filed called disp. the file is set to short text. The value in the original table is F10.00 but when the process is run the field read in as "F10.00". I have tried  
Replace(Disp, Chr(34), "") but it doesn't work. Does anyone have any other ideas how i can get rid of the quotes marks? Thanks in advance.
0
What is wrong with this?  (Note:  Full Name is text and AccountID is number)

Set RS = CurrentDb.OpenRecordset("Select * from tblContacts where [FullName] = '" & Me.txtFullName.Value & "'" And [AccountID] = Me.txtAccountID.Value & ")
0
I have a database providing bid options to clients. Every bid has a "GOOD-BETTER-BEST" option - always. I want to display the data on the report with the headings on the far left and then the "GOOD-BETTER-BEST" data across horizontally... kind of like a slot machine. I have tried a cross-tab query and it will not allow me to show all the details the way we need to. Please help or advise if this simply is not possible in Access 2016
0
Hi -
I have an app that was written in 2022 in 32 bit. I need now to revisit it and migrate to 64 bit. I am getting the error:

Screen-Shot-2017-06-27-at-14.43.59.png
can anyone help?
thanks in advance
joao
0
Tables are related 1 to many in a structure:
Parent Folder
       Sub-folder
             Files

Parent Folder holds the Main starting Folder Name
       Sub-Folder holds all of the names of the individual folders in this first layer
              Files holds all of the names of each file contained in each sub-folder.

Can this be done using VBA ?

Example Data:

Parent Folder:  2017-06-29
      Subfolders:  1, 2, 3, 4, 5, 6, 7, etc (always numerical)
            Files:  (In Subfolder 1: 01.pdf, 02.pdf, o3.pdf)

Or, do I just record the entire details of the structure and then break it out with an action query ?

Thanks,

Bill
0
I'm building a Microsoft Access application with a login authentication feature, I wanted to avoid the need for users to enter a login and password each time they need the use the app and then thought of using biometrics . How can I implement the use of a biometric login to a Microsoft Access application.
0
Enroll in June's Course of the Month
LVL 9
Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

I am building a recruitment application and need to add a function that would activated by pressing a button after inserting a contract ID, contractors ID, Clients ID and a start and end date for the contract. It should then automatically build a time sheet for that contract, it should build a new contract record in a parent table storing Contract ID, Contractor ID and Client ID and then a MULTIPLE week by week (numbered) time sheet into a child table only looking at the start date always a MONDAY and END on the end date ALWAYS a FRIDAY (say 3 month long so 12 records/weeks) and should be ONLY WEEK DAYS not WEEK ENDS (MON through FRI) in each numbered week (NOT WORRIED ABOUT HOLIDAY), a new record in the TABLE holding the following information:
unique id
contract id (foreign link) (which will be linked from five record in a week)
each day date
each day Title Monday, Tueday, Wednesday etc

The two Tables involved  are  a Contract's parent table which will hold the contract id and a contract's child table that will hold a week numbers, dates and days of the week
0
I have attempted to address this with endless changes of periods and exclamation points, .Form, .Mainform, .Subform, to no avail. Here is the code at the moment that doesn't change the text in the textbox Text41 and also does not place the value of Author in the main form in the corresponding field in the subform based on a Combobox, Frame59. Suggestions are appreciated.

If Me.Frame59 = "2" Then
Me.Anthologies.Visible = True
Me.Anthologies.Form.Text41.ControlSource = "Anthology"
Else
If Me.Frame59 = "3" Then
Me.Anthologies.Visible = True
Me.Anthologies.Text41.Value = "Collection"
Me.Author = Form.Author
Else
If Me.Frame59 = "1" Or "4" Then
Me.Anthologies.Visible = False
End If
End If
End If
End Sub
0
I have a combobox whose rowsource is a query. It should display 4 columns. The problem is that when it is first run the 4th column does not display. If I close the form and reopen then the 4th column will display. I've determined that the query is correct but the column returns a null value. I have tried requery but it doesn't help.
Thanks
Ray
0
Im not sure this is possible but Ill ask anyway. I need to import a report that's vertical and need to transpose it horizontal like a regular record. So for example column A5 thru A32 would all be headers and column B5 thru B32 would be all the values. Is it possible to get that into a standard record file format going horizontal like a normal data record.
0
How can I read csv file as an array and access those values in future
0
I have a large table with about 40 fields (and it is normalized).  The mainform handles many of the fields.  On the mainform is a tabcontrol.  One of the pages of the tabcontrol is a subform that holds another tabcontrol.  By doing this it groups fields under the proper page name.  On the mainform is a SAVE button that uses an INSERT sql statement and an UPDATE sql statement.  All the fields in the mainform, tabcontrol, and the one subform tabcontrol all do to the same table.  

What I am running into is getting the subform tabcontrol info to update (or even insert) into the table.  Here is one of the fields (rather than all of them) I need to update/insert.

        sqlUpdate = "UPDATE dbo_tblLocation " & _
                    "SET COPE_ConstructionTotalSqFt = Me.sfrmPropertyCOPEChild.txtConstructionTotalSqFt " & _
                    "WHERE LocationID = txtLocationID "

It seems regardless of the way I work the SET line after the '=' sign, I get the 'Enter Parameter Value' notice.  I have been googling thru and trying every suggestion I've read and the same continues.  I even put the field on the mainform and was able to property vba the sql statement to insert/update the field to the table.  But now that the field is on a tabcontrol on a subform inside a subform control I feel stupid.  This isn't the first subform style I've worked with.  

Anyone got any suggestions, thoughts, comments, or words of wisdom on what am doing wrong (other than changing profession)?…
0

Microsoft Access

216K

Solutions

178

Articles & Videos

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.