Only one record can be current

I have a table with fields for the year, dues, and a Boolean field that indicates whether or not its record is the current year record.

I have a form based on the table that is used to update the table.

My business rule is that only one record can have the "current year" field checked.

I am working with the Form_Before_Update event. What I would like to happen is if the person selects the "current year" check box and if there is already another record with its check box selected, my code will realize that the user has selected a second record and undo the check box and cancel the event.

Question: would anyone know of code that would check for the second record being checked and back out the user's check for that record?

Note: I have to account for not only the user clicking the record selector to try and save the record, but also the case where they check the box, then click on the "close form" button that I created.

Only one record can be current

Progress Bar

Our office has purchased a new Windows 7 computer and we are using MS Access 2003 to send Email to Outlook 2003

On our existing XP machine with the same Access to Outlook configuration our Email is being sent without a problem

On our Windows 7 machine we must be missing something as we receive the following message
Forms!ProgressBar!progBar.Max = ContactCount

I remember that we had to add a file which I think was - Mdac - to have our program work
Could I please receive a suggestion on where to obtain the missing file
Thank you for your assistance

Linda

Progress Bar

Question Yahoo Broke

I have an Access database that goes to http://ift.tt/1Pbt921 and gets me about a dozen numbers/dates for each stock ticker I send it. And I loop through about fifty companies. Been working for over a year, really got to rely on it. Now on Monday, the code failed. The page cannot be located. Does anyone out there download stock information, for free of course, from another site? I could really use that URL. I suspect the new boss at Yahoo, after canceling their work from home policy, is now tightening up on the freebies. And I don't need up to the second data, twenty minutes old is fine, I just don't want to type all that in manually. Thanks in advance.

Question Yahoo Broke

VBA Looping

Hello,

Is it possible to run a loop through each record in query, but refresh the query in between loops?

The query I am looping needs to be updated before moving to the next record.

Thanks for any help. I have found plenty of advice on how to run through each record, but can't figure out how to get the query to refresh before looping.

Thanks,
John

VBA Looping

Printing a pdf of the master report and skip subreports with no Photo and descriptive

I am needing some help with the subreports that contain images and text boxes.
I have a Master Report with 4 subreports. Each subreport contains 2 images and 2 text boxes.
I am trying to get the Master Report to not print the subreports if there is no descriptive text in the text boxes.
I have attached my database. I am using Access 2010.

My workflow:
1. Open Input Form
2. Use the drop down list in the upper right to select a Structure Number
3. Select the Photos tab
4. Click the Insert File button for Photo1_Path
a. Browse for a photo image. This only captures the file path and does NOT attach the image into the database.
5. Type in some descriptive text in the Photo1_Description field.
6. Save Form.
7. Open Print Form
8. Use the drop down list to select a Structure Number.
9. Click the Print to PDF button.
10. Select a location to save/output the pdf file.

Here is the location of the code that I am trying to get to function so my subreports with no descriptive text will not print.

Master Report
In the Master Report, Detail Section, I have an Event Procedure in the “On Print”.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If (IsNull(Reports![Master Report]![Photo1Report].Report!Photo1_Description)) Then
Reports![Master Report]![Photo1Report].Visible = True
Else
Reports![Master Report]![Photo1Report].Visible = False
End If
End Sub

I get this error: Ambiguous name detected: Detail_Print.


Photo1Report
In the Photo1Report, Report Section, I have an Event Procedure in the “On No Data”.
Private Sub Report_NoData(Cancel As Integer)
If Me.Photo1_Description.Report.HasData Then
Me.Photo1_Path.Visible = True
Else
Me.Photo1_Path.Visible = False
End If
End Sub

The Photo1Report, Detail Section, I have “Can Shrink” set to yes.
With the code for Photo1Report, I get errors when trying to use the Input Form “Insert File” for Photo1_Path.
Also, the Print to PDF button no longer finds the Structure Number and a window pops for input.


Any help is greatly appreciated.
Thank you.

Attached Files

Forum_Help_Database.zip (268.4 KB)

Printing a pdf of the master report and skip subreports with no Photo and descriptive

Trying to auto populate a subform

I've got a Parent form (frmProspectDetails) with a subform linked (fsubProspectSkill).

This form is for users to enter general information on a prospect (names, position, height, weight, etc) on the parent form and skill levels on the subform.

Currently the subform is set so the user has to manually select the specific skill set for the prospects position, and then enter a value for each skill level(1-99).

I'm trying to get the subform to auto populate with a specific skill set based on the position selected in the parent form, so the user only needs to input the skill level without selecting each specific skill.

This data is then stored in a table (tblProspectSkill)

To add, I've created a form (frmPosition) that lists the specific skills set for each position.

I'm not sure how to get this subform (fsubProspectSkill) to auto populate with the specified skill set from frmPosition.

Attached Files

Madden2016FMtest.accdb (800.0 KB)

Trying to auto populate a subform

Creating a text file from VBA and exporting data to .txt

First I'm developing in Access 2010.
I am struggling with code that is supposed to create a text file and populate it from a query. The Query "cbt_Candidate_Export_Temp" is working fine but I keep getting the error in the second argument:

"Run-time error '3625': The text file specification 'cbtTab' does not exist... "

DoCmd.TransferText acExportDelim, "cbtTab", "cbt_Candidate_Export_Temp", "\\denali\nbcc\databases\BCC\CBT_Export\" & strFileName & ".txt", True

What object is "cbtTab"? I didn't write this base code and I can't find it nor do I know how to create "cbtTab"

Thanks in advance!

Creating a text file from VBA and exporting data to .txt

office upgrade

Hi Everyone,
I work in a small office where we are still using office 2003 pro. I would like to upgrade to office 360 2013 and I am wondering if I can safely convert my access tables, forms, etc. directly from 2003 or do I need an in between version 2007 or 2010?
Thanks.

office upgrade

Urgent

Does anyone know what the code below means??

'" /x Marco1",

Urgent

Limit Date Values to Quarters

I have a field that shows dates as quarters. Right now it is just a date/time field, but that posses a problem, as the user can enter any date possible. I want the dates to be limited to March, June, September and December, and any year they choose. Can someone please help me with this. I tried fooling around with the input mask and the validation, but I just couldn't figure it out.

Thanks in advance

Limit Date Values to Quarters

Budget table design

I have a table that tracks, monthly and yearly, budgets and actuals for head count. As of now I have columns for month, year, budget, and actual. Recently i have thought that changing the budget and acutals columns to type and value would be more appropriate.

For example:

Current: CalMonth, CalYear, Actual, Budget

New: CalMonth, CalYear, Type(budget or actual), Value

Which one is more appropriate?

Budget table design

Archiving Data - Best Method?

1. Will archiving older data improve response time of a database?
2. What is the best way to archive data knowing that older records will still be accessed twice a year?

Archiving Data - Best Method?

how to Update ODBC linked tables on ms access

Hi All -

Does anyone know how to Update ODBC linked tables on ms access? Really appreciate any insights. Thanks in advance!

how to Update ODBC linked tables on ms access

Crosstab Queries Columns Heading limitation

I was wondering how to do a crosstab query and have to column headings

I need the Organization Number and the Org name..so something like this

4005 4010

Office of HQ Office of Accounting

Is this possible?

Crosstab Queries Columns Heading limitation

Novice question about tables

When a table is completely refreshed (as in cleared and reloaded with new data), as it is being reloaded, do all result queries (queries that produce another table) that are based on the reloaded table get refreshed as well?

Thanks.

Novice question about tables

SQL null dates

Hello. I tried but couldn't make a sql line work. If you give me a hand, I'd be very very grateful.

I'd like to put a WHERE clause where I confont the date in an unbound textbox with the date in the table. In vba I control the null case of the unbound textbox, but I'm not sure how to control the null value in the sql code.

here is the code "strDataRiferiment" that I later insert in a concatenated SQL code. The rest of the code works, while this line doesn't. The problem are the null date records present in the table COMMESSE.

What I'd like is a WHERE clause that:
- returns records with all null date values and
- returns records with not null values where these dates are < date chosen in the unbound textbox

Code:

---


Select Case Nz(Me.InizioDataRif.Value, "Null") 'the unbound textbox
      Case "Null"
            strDataRiferimento = "AND (([if is not null] COMMESSE.DataChiusura =< #" &  Format(Me.FineDataRif, "mm\/dd\/yyyy") & "#)  OR (COMMESSE.DataChiusura IS NULL)) "
      Case 'etc
      'etc..
end select

---

I tried for example:

Code:

---

"AND ( IFF ((COMMESSE.DataChiusura,0) IS NOT NULL, 
(COMMESSE.DataChiusura,0) =< #" & Format(Me.FineDataRif, "mm\/dd\/yyyy") & "#) , (COMMESSE.DataChiusura) IS NULL) )) "

---

Help?

SQL null dates

Hiding Social Security Number in form

Hi all,

This should be relatively easy although I seem to have forgotten a lot since the last database I completed. I have looked through several examples online and I am lost. :confused:

On my form frmEmployeeDetails I have a text field that is named SSN for social security numbers. I have used the mask on it so it automatically inputs dashes. I am attempting to hide all but the last four digits of the ssn. This is on the on current event of my form.

Code:

---

'Hide SSN
Dim strLSSN, strSSN As String
strLSSN = Right(SSN, 4)
strSSN = ("***-**-" & strLSSN)
Me.SSN = strSSN

---

Any help is greatly appreciated, Thanks!
Samantha

Hiding Social Security Number in form

Query Criteria Help Please

Hello,
I have a Query called [Trending Report Query] that is pulling from the table [Monthly Trending data]. I have a form called [Monthly Trending] that I'm using to specify what trending date I'm pulling from (it is in format mm/dd/yyyy). In the field criteria of the Query, I have

[Forms]![Monthly Trending]![Trend]

but I need it to pull the date in the form and the 2 months prior to that. For example, if I put 02/01/2015 in the form as my trending date. I need the Query to report out on all that have the dates 12/01/2014, 01/01/2015, and 02/01/2015. What does my criteria need to be? I can't figure it out and google is no help.

Thank you in advance

Query Criteria Help Please

insert HtML file in to body of email (inside loop email)

Hey everyone - thanks for your help
- this works a treat
but now I want to enhance it

what i want to do is inset a html file into the body of the email
I have created a publisher file and saved as html ..say

C:\test\pub1.html
:banghead:

how do I get this in to the body ?

code to date below (not the tidiest - but it works)



Private Sub Command0_Click()


Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset

Dim OutApp As Object
Dim OutMail As Object
Dim strAttach1 As String

Dim Attachment As String
Dim RefernceNumber As String
Dim attachments As String

Dim FilenameZ As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("bondqry", dbOpenSnapshot)
Dim FilterZ As String
Dim strID As String
Dim i As Integer



For i = 0 To rsEmail.RecordCount


rsEmail.MoveNext
Next i


With rsEmail
.MoveLast
.MoveFirst


Do Until rsEmail.EOF
DoCmd.OpenReport "Bondreport", acViewPreview, , "RefernceNumber=" & rsEmail.Fields(6) & ""
DoCmd.OpenReport "Bondreport", acViewPreview, , "RefernceNumber=" & rsEmail.Fields(6) & ""

DoCmd.OutputTo acOutputReport, , acFormatRTF, "c:\Test\" & .Fields(6) & ".Doc", False
DoCmd.Close acReport, "Bondreport", acSaveYes


strAttach1 = "C:\test\" & rsEmail.Fields(6) & ".doc" & ""


If IsNull(.Fields(11)) = False Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail


.to = rsEmail.Fields(11)
.Subject = "" & rsEmail.Fields(6)
.HTMLBody = "some blurb.. tied to a table ? " & vbCrLf & _
"Field A: " & vbCrLf & _
"Field B: " & vbCrLf & _
"Field C: "
'.display
.attachments.Add strAttach1


strID = rsEmail.Fields(6)


.send

End With
End If
.MoveNext
Loop

End With




Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

insert HtML file in to body of email (inside loop email)

Two tables in query

Hello All...
Please Help .

I have two tables (1- User) , (2-Dept) have filed name D_Sum and link ID of [User] to UserID of [Dept] table

My question is how to add both of tables and get all user with filed name D_Sum form [Dept] table :confused:
in order to filter User with that have dept or not

Note: I try to do it but I get only user with dept :banghead: ,, I need all user

thanks ,,,,,,

Two tables in query

Disappearing Custom Menu

I have a database used by many different people where the front end resides on their PC and the back end resides on a shared drive. It has auto update functionality so the users always have the latest version of front end. This database has been in use for years and uses a custom menu bar. I know that this feature is not available (technically) in newer versions of Access but by not converting to accdb format it has remained functional even in Access 2010. For everyone it works perfect, except one person. For that person the menu disappears when the Db loads. I have verified that he has the most recent front end and I am stumped as to why it is not showing up. I suspect it may have something to do with resolution of his monitor? But that hasn't helped. Any ideas out there?

Disappearing Custom Menu

Question Table structure for multi location warehouse

Hello all,
I am working with Access 2007. I have a pretty good working knowledge of access. Here is my problem. I have a warehouse which supports four departments. The warehouse floor area is segragated into four areas. Area 1 department 1, area 2 department 2, and so on. Each department uses the same commodity, so I will have four of each item, belonging to each department. The storage locations will be unique and not repeated anywhere in any department. I receive and issue items for all departments. The problem is how do I structure the tables and queries to receive an item for department 1 adding it to department 1 only and not have the item added to another department with the same item. Also to issue the item from a department 1 without effecting the on hand balance of any other department. I have been pulling my hair out trying to figure this out. I am too the point of creating 4 separate databases one for each department.
:banghead:
Need help very bad.
Wansa

Question Table structure for multi location warehouse

Access VBA Crashes, No Error Codes

I am having a serious issue with some of my VBA code in my Access front end. It links to a SQL database. Here is my code:

Code:

---

Private Sub New_Record_Click()
    Dim MySerial As String
    Dim rsCriteria As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim Feedback

    Set db = CurrentDb
    Set rs = db.OpenRecordset("FourWire", dbOpenDynaset)

    On Error GoTo ErrorHandlingCode

    MySerial = InputBox("Serial #", "New Part")
    rsCriteria = "[Serial] = '" & MySerial & "'"
    If MySerial = "" Then
        Exit Sub
    End If

    rs.FindFirst rsCriteria                            'Search for existing record
    If rs.NoMatch Then
        'Good, so lets make a new record!
    Else
        Feedback = MsgBox("Record for this part already exists!" & vbCr & "Would you like go to the record?", vbYesNo, "Warning")
        GoTo LoadExistingRecord
    End If

    'Me.FilterOn = False                                        'turn off filter
    DoCmd.GoToRecord , , acNewRec
    [Serial_FourWire].value = MySerial
    DoCmd.Requery
    rsCriteria = "[Serial] = '" & MySerial & "'"
    'Me.[Black_Blue].SetFocus

    rs.Close

    DoCmd.Echo False
    Forms("Four_Wire_Test").Recordset.FindFirst rsCriteria
    If Forms("Four_Wire_Test").Recordset.NoMatch Then
        MsgBox "Could not find record just created! Contact your administrator."
    End If
    DoCmd.Echo True

    Exit Sub

LoadExistingRecord:
    If Feedback = vbYes Then
        Forms("Four_Wire_Test").Recordset.FindFirst rsCriteria    'User clicked "Yes"
        Me.[Black_Blue].SetFocus
    Else
        'Just Quit                                              'User clicked "No"
    End If

    Exit Sub

ErrorHandlingCode:
  Dim strError As String
  Dim errLoop As Error

  For Each errLoop In Errors
        With errLoop
        strError = _
          "Error #" & .Number & vbCr
        strError = strError & _
          "  " & .Description & vbCr
        strError = strError & _
          "  (Source: " & .Source & ")" & vbCr
        strError = strError & _
          "Press F1 to see topic " & .HelpContext & vbCr
        strError = strError & _
          "  in the file " & .HelpFile & "."
    End With
    MsgBox strError
  Next
End Sub

---

I have managed to isolate the crash, which entirely takes down Access 2007, without a fault. The line is:

Code:

---

    Forms("Four_Wire_Test").Recordset.FindFirst rsCriteria

---

One note is that I have used this code successfully before in other places of my front end. "rsCritera" is equal to "[Serial] = '1234567'". I have confirmed that "1234567" exists in the table "FourWire".

Does anyone have any recommendations as to what I am doing? Why access is just crashing and not able to give me a fault code or anything? Any help is much appreciated.
:banghead:

Access VBA Crashes, No Error Codes

Select multiple list items from a paste

Hi,

I have a list with 50 items in it, I would like to select some of these items by pasting in a coma separated list, is this possible?

Thanks

Gary :D:D:D

Select multiple list items from a paste

Copy Database

This is probably a stupid question I no but I need to ask before I do it.

I want to make a copy of an address database that we use, delete all of the addresses and start fresh for a separate company.

I have simply "saved as" and changed the name so that there are 2.

If I start deleting things from the copy database, will it delete from the original?

Like I said I no it sounds stupid but theres 1300 + addresses that I cannot afford to delete.

Thanks for your help in advance.

Sarah

Copy Database

filter report

I have a report filter that filters the reports by month and year:

Code:

---


        DoCmd.OpenReport "AttWholeCity", acPreview, , " Month = '" & txtCourseDateMonth & "' AND Year = '" & txtCourseDateYear & "'"

---

I want the code to also show these two columns where there are null/blank values aswell, for example if I filter by apr 2015 i want the report to show these columns as well as blank columns is this possible

filter report

reference "parent" description in hierarchical data table

I used to be able to do this...about 2 years ago. It's amazing what you forget when you don't use the skills....

I have a table I'm trying to query information out of. Key fields are below:

• RecNo (Key)
• ParentRecNo
• Description

I need to have a "record" in the query show both the description of itself, as well as it's parent. This is quick and dirty; I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.

reference "parent" description in hierarchical data table

Upgrade from 2003

I work as a developer for a large corporate co..

We have moved to a Citrix environment, so the users run a standard build, everyone running Windows 7 64 bit..

The infrastructure team are looking to roll out either Office 2010 or 2013.. I'm yet to find out if this will be 32 or 64 bit..

I am aware that DECLARE statements have to be changed to run on 64 bit..
I'm also aware that there was a change with Excel vba charting model from 2003 to 2007..

I've also read this article on discontinued features..
http://ift.tt/1bXR4Wr

What issues have you encountered when upgrading ?

Upgrade from 2003

One to Many Table Calculation

Hello,
I'm currently using a Dlookup function to display a calculated field from a query. This works fine when only one record is entered on the many side; however when additional records are entered it doesn't add the additional records. What function/code can I use to add each record's total as it's entered on the many side?
This is what is working when the many side only has one record:
=(DLookUp("[qryTotalBalance]![NetBalance]","[qryTotalBalance]","[CustID]= " & Nz([CustID],0)))

I'm at a loss as to how to go about this...

Thank you for your help in advance.
NLR:confused:

One to Many Table Calculation

Query inserts wrong data in the table

Hi Guys

I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.

Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.

Code:

---

INSERT INTO tblpostroom ( Date1, 582, 1810 )
SELECT Date1, 582, 1810
FROM Sheet1;

---

I think I am doing something wrong in the query.Can anyone please help me to resolve this problem?

Query inserts wrong data in the table

VBA Certification

Dear All,
Just want to know that am currently developing application using access VBA and macros, so please let me know valuable certification for VBA developer or Macros Specialist and any specific Exam or course for this certification?
Example: MCPD or Cisco something like that... :)

VBA Certification

Query Help!

Any experts there to help me on the below query issue.

http://ift.tt/1zsiGgU

Regards,

Paul C:(

Query Help!

auto fill

How to fill values in a new record with data from previous record please?

I've total 11 columns in a table and values in 3 4 columns are repeating for few rows before it needs to be changed eventually. I want to fill these rows with values from previous record.

Thanks.

auto fill

Default Error Messages

how to change Access default error messages with custom messages?
Like, if I enter some data in a sub form where the parent form is empty new record, default error message says that 'You must enter a value in the 'column name' field'.

I want to disable this default message and display my own error message.
Now I know MessageBox and MsgBox fuctions to display custom messages, but how to disable the default message please.

Default Error Messages

TransferDatabase, Append question

I'm trying to find an elegant solution to an import issue using the TransferDatabase command (Access 2007). I haven't found a good solution yet, and have read a few posts, including a couple from this site related to this issue. If someone could point me to a link with a good solution, I would appreciate it.

Here is the issue...I have a project with a "table free" frontend main program, with linked tables to the backend containing the actual tables. There is a button that the user presses to import various DBF file(s). When the TransferDatabase command is run, it has no append option that I am aware of, so a table is created each time this happens. Yes, I have code to delete the existing "temp" table so that a new one can be created with the desired name, but the table is created in the frontend, and not the backend,which is where I would like it to be if no append option exists.

This is not a major issue, I'm just trying to find a way to make it "cleaner" if possible. This means the frontend database, over time, will continue to grow due to this temp import file being created and deleted for each import, and it will need to be compacted. I already even created two automatic routines to compact and repair the backend AND the frontend databases if each exceeds a certain file size.

I'm not going to bore everyone with all the code, but I have a function to check if the import table "TempX" exists, then delete it if it does, this way I don't get TempX1, TempX2, etc because the TransferDatabase command doesn't overwrite the table if it exists, but creates a new one with a number at the end

If TableExists("TempX") Then
DoCmd.DeleteObject acTable, "TempX"
End If
DoCmd.TransferDatabase acImport, "dBase IV", GrabDir(.SelectedItems.Item(1)), acTable, GrabFile(.SelectedItems.Item(1)), "TempX", False

Everything works fine, except as I've said, the TempX table is created in the frontend database. A simple append option in the TransferDatabase would solve everything, because I can then append the imported data into a linked table in the backend, but don't see how it can be done. I just would love it if there were a way to import a DBF file into a program without actually having to create a new table every time.


Any good solutions out there?



Thanks

TransferDatabase, Append question

Generating a random time from list in query

Hi,
Hoping someone can help. I am trying to create a database to link to an online auction site (not ebay) and I want to create a query that randomly generates auction end time and auction length for all listings. I have tried int(rnd()) and various versions but can only generate the same number for all fields in the query.
Any ideas?

Its been 20 years since i last used access so its a bit of a relearning curve!
Thanks in advance for any info given

Generating a random time from list in query

Linked sharepoint tables don't sync?

I have 2 separate Access 2013 Web Apps on a Sharepoint 365 server.

Web App 1 was created by uploading related tables to the Sharepoint Azure SQL. I am hoping to use that app strictly for housing the tables which are currently linked to a desktop application which is working very well.

I'm currently working on Web App 2 which will allow remote data entry into the tables of Web App 1. I tried to linked the Web App 1 tables to Web App 2, through an ODBC connection. The tables and data are all there. If I update data via the Desktop application or within Web App 1, it does not reflect the changes in the tables of Web App 2.

It seems as though these tables where imported rather than linked. Although there is an option to Link Sharepoint lists, these are actually Azure Sql Server tables, and are not listed in the 'list' of Sharepoint lists.

Am I out of luck in creating Web App 2 for my purposes, or is there a way to actually link the tables from Web App 1?

Thanks!

Sup

Linked sharepoint tables don't sync?

Stuck at ADODB Collections

Hi friends

This is the first time I try to use “collection” of a ADODB.recordset eg rsTarget, so need some fast help.

Essentially I want to assign the fields (columns) in this rsTarget with respective data. I tried to use of two sets. (fld as adodb.field)
Set fld = rsTarget.Fields.Item(count)
Set fld = rsTarget.Fields.Item(Column(count))

Then I try to use following to assign
RsTarget!fld.name = “Data1”

It failed stating that there is no fld.name. Using column set, it state that there is no ‘column” function. How to do it right? Thanks mates. :)

Stuck at ADODB Collections

Excel Connection to Access Query

Hello everyone,
Using Excel and Access 2010..
I have created a connection from Excel into and Access Query using the "from other sources\from Microsoft query" option. (Images ConnectionType and ConnectionType02)
This then pulls in data from an access database query and Excel uses this data to formulate figures and produces charts accordingly.
Works brilliantly on my laptop (Excel) into our work terminal server (Database)

The Problem;
When I place the excel worksheet onto the Terminal server for the company to use, I get Connection errors. (Images ConnectionError01,02,03).
Majority of staff are using an RDP type set-up to connect into the terminal server. We have a multi site company.. lets not go there why its set up the way it is :banghead: The terminal server is using Excel 2010 and Access Runtime for the Access programs.
I split the database into a front end and a back end and save the front end as a .accde file.
Can I please get assistance to rectify this connection issue so I can appease the powers that be and they can use what I have created.

I have created a macro command button on the spreadsheet which fills in some cells with dates and then calls the refresh all data function to activate the query connection.
Appreciate the help.

Attached Images

	ConnectionError01.jpg (34.4 KB)
	ConnectionError02.jpg (33.0 KB)
	ConnectionError03.jpg (30.2 KB)
	ConnectionType.jpg (58.4 KB)
	ConnectionType02.jpg (67.2 KB)

Excel Connection to Access Query

Emergency plz help.

I have this problem and cannot figure out for the life of me what I am doing wrong or what I am missing. Any help would be appreciate please and Thank you in advance.

Private Sub Form_Current()
'If the Expiration date value is ealier than January 1, 2017
'display the words "Time to renew"
'in label control to the right of the Expiration text box in bold, and red,
'display the text in the Expiration control in red
If ExpirationDate < #1/1/2017# Then
lblExpirationMsg.Visible = True
ExpirationDate.ForeColor = vbRed
End If
lblExpirationMsg.Visible = False
lblExpirationMsg.ForeColor = vbBlack
End Sub

Emergency plz help.

TextBox [Date]+30

Hello All ..
Please Help ..

I have 2 bound TextBox ( Date & ValidityDay ) on form base on query and query base on table

I need ValidityDay TextBox get and display value from [Date] TextBox that I pick it +30Day and return this value to table and save it when I press save record button

Thanks...

TextBox [Date]+30

Where You From

I just notice a new joiner peter 101 is from Holland. I thought it might be interesting to see how many places are represented, If you think so please join in. If each person does a quote as they add there place it will keep the list in order. I will start
USA, Florida

Where You From

Excel - Speed up VBA process for loop in formatting - the numbers

A little rehash of speeding up things using Excel Automation.
Using MSAccess, a Recordset of just over 30,000 records is pulled from SQL Server and placed into Excel using Excel automation.

The last Column 0 contains a number of 1 to 4.
Sort is by "Well Name" then by the 1 to 4.

This means that every time there is a new Well Name - there is a cooresponding number 1 in column O.
The rows with number 4 are a zero to many for the Well.
All together, there is a about 10,000 number 4.

The code has a loop to check each record to the RecordSet.Count.
Inside the loop, the Code consist of an If - Then - each with a nested If-Then.

Running this on a Pentium 3 Gen i7, the code with out the speed up took 7.75 Minutes to complete (just the loop)

Code:

---

' speedup ////////////////////////////////////////////////////// speedup code///  turn off to troubleshoot or debug
  objxl.ScreenUpdating = False
  objxl.Calculation = xlCalculationManual 'To turn off the automatic calculation
  objxl.EnableEvents = False
  objxl.ActiveSheet.DisplayPageBreaks = False
' speedup ////////////////////////////////////////////////////// speedup code///  turn off to troubleshoot or debug
 
2790    With objxl.ActiveWorkbook.ActiveSheet
          'objxl.ActiveWorkbook.ActiveSheet
2800          For i = intRowPos To intMaxRecordCount + intRowPos
2810            'If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
                If .Cells(i, "O").Value = 1 Then  ' 1st sorted order for Lease type
2820                    .Range(.Cells(i, "B"), .Cells(i, "J")).Font.FontStyle = "Bold"
                        .Range(.Cells(i, "B"), .Cells(i, "J")).Borders(xlTop).ColorIndex = xlAutomatic
                                    ' must set back to automatic xince Else statement changes style
                        .Range(.Cells(i, "B"), .Cells(i, "J")).Borders(xlTop).Weight = xlThick
 
2830            Else
2840                .Range(.Cells(i, "B"), .Cells(i, "F")).Font.ColorIndex = 16 'metalic gray
                    If .Cells(i, "O").Value = 4 Then
                            .Range(.Cells(i, "G"), .Cells(i, "H")).Font.FontStyle = "Bold"
                    End If
2850            End If
2860        Next i
2870  End With
          ' end bold columns on changed value  7.75 min for loop
        ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Bold and Lighten <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' speedup ////////////////////////////////////////////////////// speedup code///  turn off to troubleshoot or debug
        objxl.ScreenUpdating = False
        objxl.Calculation = xlCalculationManual 'To turn off the automatic calculation
        objxl.EnableEvents = False
        objxl.ActiveSheet.DisplayPageBreaks = False
' speedup ////////////////////////////////////////////////////// speedup code///

---

With the Speedup Code as shown above, the same process took
1.25 Minutes.

Excel - Speed up VBA process for loop in formatting - the numbers

Hello

Im a long time user of this site but never got round to registering until now.

Been tinkering around with access for many years and have made several databases that we use on a day to day basis within my organisation, however Im no where near the level i would like to be.

Anyway nice to meet you all.

Hello

Disney lays off IT workers, imports foreign workers H1-B Visa Lobby increases numbers

http://ift.tt/1Ahf7oK
Southern California Edison recentlly did the same with its employees. Any employee who complained was threatened. The investigation has to grant employes a way to secretlly testify to Congress. The H1-B law isn't being enforced. It is only to be used if there is no US Worker available to do the same job.
Microsoft, Oracle, Google, and many companies lobby Congress to take advantage of the H1-B corporate tax deduction that involves bringing foreign workers to the US.

"Team Disney" threw IT workers, some who received the excellence awards out of the wonderland.
This is not outsourcing, sending work overseas. They replace existing US workers, often for the same pay (to the job brokers), by importing foreign workers with the H-1B visa. From the Corporation point-of-view these foreign workers are cheaper.

Bill Gates and a host of other wealthy corporation owners swore to Congress that there is a dire shortage of IT workers in the US. Many in Congress respond to the powerful H1-B Visa lobby. The tax incentives are so powerful, companies can't resist.

Now the lobby wants to rais the yearly cap to open the flood gates. The GAO (Government Accounting Office) admits, they have lost track of how many have entered and even where the previous H1-B visas are today.

Lobbies are much more powerful than Democracy in the US.

Disney lays off IT workers, imports foreign workers H1-B Visa Lobby increases numbers

Filtering forms.... :/

Hey guys, I just have a (probably) really simple question for you. I'm using a datasheet that can be filtered for certain fields. I have a macro that opens a form to the record that was selected (aka "current") in the datasheet. It filters this opened form to "1 of 1" which makes sense. How would I go about filtering that form for all of the records left (after filtering) in the datasheet?

PS: I hate macros, but I'm doing client-specific work.
PSS: The primary key is a field (autonumber) called "ListingNumber".

Filtering forms.... :/

Include special characters in export

I'm trying to export either a report or a query to excel with a field name that has special characters "()". I wouldn't normally ever do this (everyone knows not to use special characters) but we're using this to import the excel document to a website, and the field name MUST be what they specified in order for the import to be successful. Is there any way to rename the field name at export, since I can't use special characters on the query or report itself?

The field name is currently DepType, but it must be
"
*Dependent Type
Spouse/Partner or Child/Dependent
(Required for Dependents only)
"
EXACTLY or it will not import. (ridiculous, I know)

Include special characters in export

for each

hi

i have the following code
i want to add for each in this code but i don't know how.
i want for each application that is fined the code will be run. can you help me please?

Function fCloseApp(pClassName As String) As Boolean
'Usage Examples:
' To close Calculator:
' ?fCloseApp("SciCalc")
'
Dim lngRet As Long, Hwnd As Long, pID As Long
Hwnd = apiFindWindow(pClassName, vbNullString)
If (Hwnd) Then
lngRet = apiPostMessage(Hwnd, WM_CLOSE, 0, ByVal 0&)
Call apiGetWindowThreadProcessId(Hwnd, pID)
Call apiWaitForSingleObject(pID, INFINITE)
fCloseApp = Not (apiIsWindow(Hwnd) = 0)
End If
End Function

the rest of the code is below:

Code:

---


Private Const WM_CLOSE = &H10
Private Const INFINITE = &HFFFFFFFF
Private Declare Function apiPostMessage _
    Lib "user32" Alias "PostMessageA" _
    (ByVal Hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) _
    As Long
Private Declare Function apiFindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassname As String, _
    ByVal lpWindowName As String) _
    As Long
   
Private Declare Function apiWaitForSingleObject _
    Lib "kernel32" Alias "WaitForSingleObject" _
    (ByVal hHandle As Long, _
    ByVal dwMilliseconds As Long) _
    As Long
   
Private Declare Function apiIsWindow _
    Lib "user32" Alias "IsWindow" _
    (ByVal Hwnd As Long) _
    As Long
       
Private Declare Function apiGetWindowThreadProcessId _
    Lib "user32" Alias "GetWindowThreadProcessId" _
    (ByVal Hwnd As Long, _
    lpdwProcessID As Long) _
    As Long

---

thank you

for each

close all powerpoint files from an access application

hello,

i want that if any powerpoint application is open after closing my access app all of these powerpoint files will be closed.
i found this code for excel file and i changed it for powerpoint application. but i have an error message in this pink line?
can you help me please?
thank you?

error 438: object doesn't support this property or method

Code:

---


Public Function test()
    Dim oApp As Object
    Dim workbook1 As Object
   
 
GoTo check
check:
Set oApp = GetApplication("powerpoint.Application")
        For Each workbook1 In oApp.Workbooks
            'MsgBox workbook1.FullName
            'workbook1.Save
            workbook1.Close
            GoTo check
        Next workbook1
oApp.Quit
Set oApp = Nothing
End Function
Private Function GetApplication(ByVal AppClass As String) As Object
    Const vbErr_AppNotRun = 429
    On Error Resume Next
    Set GetApplication = GetObject(Class:=AppClass)
    If Err.Number = vbErr_AppNotRun _
        Then Set GetApplication = CreateObject(AppClass)
    On Error GoTo 0
End Function

---

close all powerpoint files from an access application

Re-centre a form?

I originally posted this query in Forms...but perhaps it better fits here? I guess it overlaps the categories? Happy for Mods to delete one.

The short tale is that I've written VBA code that hides or shows certain fields on a form depending on whether the user ticks a checkbox in the form's header. I've already written code that shuffles the columns and controls around so there isn't a gaping hole where the hidden fields are, and I've used 'DoCmd.RunCommand acCmdSizeToFitForm' to resize my form. It all works fine.

What I can't find is simple concise code to re-centre the form in its smaller size. The 'Autocenter' property of the form is already set to True, it seems bizarre there is no way to re-trigger it, or force the form to re-draw (and hence re-trigger it).

Any ideas? Thank you.

Re-centre a form?

Hide #Error on complicated calculated field

Hi folks, I have a report with some pretty complicated calculated fields. The fields work EXACTLY as I want them to, except for when the [Text98] box = "" in the code below:

Quote:

=IIf(IsNull(DLookUp("[PersonName]","qryMonthlyScheduleResults","[AssignedDate]=[Text98] And [LocationName]=[TempVars]![Loca] & ' Results AM'")),"",DLookUp("[PersonName]","qryMonthlyScheduleResults","[AssignedDate]=[Text98] And [LocationName]=[TempVars]![Loca] & ' Results AM'") & " AM")

What I want to do is hide this field if it returns a #Error, and allow it to be visible if not. I only get a #Error in this field if [Text98] is blank, and [Text98] has some complicated code, itself, to determine whether or not a date appears. Basically, the report is a weekly calendar, and in this particular field, [Text98] is the date display of the upper-left most box, which is a Sunday. If the calendar month doesn't start on a Sunday, that box should be blank, but if the month DOES start on Sunday, the box will show, for example, "4/1/2015". Again, that works perfectly, as does the field with the code block I posted, UNLESS that Sunday box shows blank (because the month begins on Monday or some other day other than Sunday), then I get a #Error with that block of code.

Is there an easy way to modify my block of code to return nothing if the value returns #Error? I've tried about ten different variations, and I think I'm getting the syntax wrong... Thanks for any suggestions you guys can give...

Hide #Error on complicated calculated field

Simple Query Criteria- Plz Solve it!

Hi..

I am trying to filter a list by using the below formula in a query. But it doesnt work especially when i input <> and the result is nothing. Could anyone fix this issue for me.

IIf([Table1].[Location]="0217",<>"075",Last([EmployeeMaster].[empno]))

Regards,

Paul C:mad:

Simple Query Criteria- Plz Solve it!

Database Platform

I have 10 small databases. At present, I have another database which serves as a platform to launch the 10 databases. I have a graphic for each database and posted an Event Procedure for each graphic to launch the respective database. For example ...

Dim accapp As Access.Application

Set accapp = New Access.Application

accapp.OpenCurrentDatabase ("M:\MPF\MPF_Mgmt_Info_System\SignInPlus\SignInPlu s.accdb")
accapp.Visible = True

Is this the best way to centrally locate and launch multiple databases or is there are smarter way? Thanks

Database Platform

Excel imported data

Hi

I have an excel sheet that I've imported into an access 2007 table. It has a field with the user name in it but every so often a location address appears in this column meaning that the users who appear below the address are based at that location. What I need to do is create another column and copy that location address for all applicable users but since the address changes every few rows it's a bit more complicated than I thought. The good thing is the location address always starts with the word "Location:" as below so perhaps that makes it easier, any suggestions. Here's how the column looks:

USER

Location: 1 anywhere street
Homer Simpson
Marge Simpson
Dave Jenkins
Location: 34 over there street
Bart Simpson
Big Jake
Lance Jolly
Mrs Murphy
Mr Murphy
Location: 56 home street
Mr Big
Mr Small
Mr Tall
Mr Greedy

Here's how I want it to look:

LOCATION | USER

Location: 1 anywhere street | Homer Simpson
Location: 1 anywhere street | Marge Simpson
Location: 1 anywhere street | Dave Jenkins
Location: 34 over there street | Bart Simpson
Location: 34 over there street | Big Jake
Location: 34 over there street | Lance Jolly
Location: 34 over there street | Mrs Murphy
Location: 34 over there street | Mr Murphy
Location: 56 home street | Mr Big
Location: 56 home street | Mr Small
Location: 56 home street | Mr Tall
Location: 56 home street | Mr Greedy

Excel imported data

Question Lost data

I designed a database in Access 2007 which is used by separate offices across our organisation. It has a main table, with a related sub-table in a one-to-many relationship.

Recently, a user in another office decided to split the database because they were having difficulties using it simultaneously with other users. I told them to go ahead but to make a backup first, in case of any problems.

It now appears that they have lost a considerable amount of data from the sub-table. The main table is fine, but when the related sub-table is opened a lot of data has simply vanished (we use forms to access the data for day-to-day use and to enter new records). Bizarrely the same data has also disappeared from the Backup.

There have been other strange occurences with this DB, such as codes going missing from a field, and a duplicate field appearing on a form. The user swears they haven't tried to modify the DB in any way, shape or form :banghead:

I'm lost for a solution. Many thanks in advance to anyone who can help.

Question Lost data

Re-centre a form?

Guys, sorry it's a along time since I posted, and I only ever seem to post queries.

The short tale is that I've written VBA code that hides or shows certain fields on a form depending on whether the user ticks a checkbox in the form's header. I've already written code that shuffles the columns and controls around so there isn't a gaping hole where the hidden fields are, and I've used 'DoCmd.RunCommand acCmdSizeToFitForm' to resize my form. It all works fine.

What I can't find is simple concise code to re-centre the form in its smaller size. The 'Autocenter' property of the form is already set to True, it seems bizarre there is no way to re-trigger it, or force the form to re-draw (and hence re-trigger it).

Any ideas? Thank you.

Re-centre a form?

Limit data entry to one of four available combo boxes

Morning All!

I'm sure I've done this before but I can't remember how...

I have a form with four combo boxes on it, the four boxes all open the same form and return a record based on the selection from a different column from the same query.

To make it work smoothly I think I need to make it so it is only possible to enter data in one combo box at a time. I could also do with some error handling.

I've had a google, but I can't get the wording right to find the help I need. I would very much appreciate it if someone could point me in the right direction....

Thanks :)

Limit data entry to one of four available combo boxes

Need your Ideas, Experiences and Helps :)

Hello My Friends;

As you can see on the picture, im creating new Navi Form. In this form there is 5 letter (these letters are symbolize names) and every letter has 3 more options (please ignore exit and home buttons) and this Month is showing report of this month and next month for next month :)

1. Question is, Shall i write (build) for every letter new Query and report ? Im asking this because if your answer is yes i should write around 100 different query and report. And it makes me crazy.:banghead: It should be shortcut somehow

2. question is, can i connect this every letter with Query ? and how can i do that on Navi Form ?

i need your ideas, helps. Im open anythings

Thanks
Y.Ö.

Attached Images

Form.JPG (43.5 KB)

Need your Ideas, Experiences and Helps :)

Filter out empty records

As the title suggests, I am looking for a way to hide or filter out the records in my reports if all of the values are 0. My problem is that all of my reports have their filters set on the event that opens the report. I use an If statement to decide which report to open, which means that different reports use different fields. This means I can't just add 'AND April_T <> 0' or something to the filter.

Is there an easier way around this?

Filter out empty records

Update multiple columns using docmd.sql "Update" command

Dear Friends,
Am trying to update multiple columns in a customer table but the statement am using is returning an error. Kindly check it for me.

DoCmd.RunSQL ("update customer set customer.paid=" & Me.Parent.txtPAID.Value,customer.balance = me.Parent.txtcontract.value+me.Parent.txtpaid.valu e)

Thanks,
Njoroge Joseph:banghead::banghead:

Update multiple columns using docmd.sql "Update" command

Count() doesn't work on one computer - does on others - same configuration

This problem has me stumped and now seeking assistance, I'm hoping someone may have come across this previously.

Microsoft Access 2010, 32 bit, Windows 7.

All office computers are configured the same way. Users reported errors of my main application, and they all originated from the same computer. Attempting to duplicate the error from other computers proved fruitless; there were no errors, all were working as intended.

I've narrowed down the problem to the following. At this particular computer the functions Count(), Sum(), Avg() and Max() do not display any results when used as expressions in Control Sources of Text Boxes on forms. There are probably other functions that don't work either; these are the ones I checked. The functions Date() and Now() do work.

The underlying table of the form has five records. When running the form the text boxes are empty. Attempting to data entry into the text boxes, the status bar correctly advises the control source is an expression. No error messages are generated either when creating the expression at design time or running the form.

When running this same database from another computer in the office everything works as intended. The functions produce and display the correct results.

I've checked references, trust centre settings and version of Access (14.0.7128.5000) and all are the same for all the office computers.

Does anyone have any clues?

Steve.

Count() doesn't work on one computer - does on others - same configuration

Creating a level per show

I would like to create a rating level from 1-5 to see how well my shows have done. To see that I would take the gross sales of the show, and negative the gross expanses. So, if like, the shows sales are $1000, and the expenses are $1, then that would be a level 5 show. Now, if the shows sales are $1, and the expanses are $100, then it would be a level 1 show. How would I do/create that? I would just need a query, with the percentages of the levels that I could change. So if total percentages of the show total after negativing the gross expenses from the gross sales is 500% more, then that show was good. Thanks! :)

Creating a level per show

Unable to add new record to table

Good afternoon everyone.
I have been using my db for 3 years without this particular problem: I am now unable to add records to one of my tables - either through the form or in datasheet view. I have a patient table and a visit table. I put in the patient data using a form and then go to the visit form to add all of the visits for the month. I have never had a problem adding data to any of my tables but now I am unable to add data to the visit table. As much as I didn't want to I brought up last month's back up to enter the patients again. Before I did I checked to see if I could add visits - I could not. So I checked January's back up - same issue. I made no changes to the db before trying to add a visit :banghead: It must be a setting somewhere.....?
So, oh smart ones, what say you? (just a basic user)

Thanks,
Renee

Unable to add new record to table

Question Access 2007 Database Keeps Closing

Hi All,

I have three access 2007 databases that used to work just fine till we upgraded from Office 2007 to Office 2013.

Now on certain occasions when we hit some buttons (that have got some macro or VB procedure behind the buttons), the databases just closes itself.

I have checked again by running the database in 2007 and it works fine, but in Office 2013 it crashes.

Any help would be appreciated.

Regards - Raj

The Button is called Create Invoice and on click the event procedure runs that has the following code behind it

Private Sub Create_Invoices_Click()
Dim rs As DAO.Recordset
Dim InvoiceNo As Long
Dim prnt As [Form_Student Data]
Dim offr As Form_Offer
10 On Error GoTo Error_Handler
20 Set rs = CurrentDb.OpenRecordset("Invoice", dbOpenDynaset)
30 rs.AddNew
40 InvoiceNo = rs!InvoiceNo
50 rs!Inv_Date = Date
60 rs!Offer_ID = Me.[Offer ID]
70 rs!Inv_Due = Date + 15
80 rs!Inv_Staff = GetStaffName
90 rs.Update
100 DoCmd.OpenForm "Invoice", acNormal, , , , acDialog, InvoiceNo
110 On Error GoTo 0: Exit Sub
120 Error_Handler: Select Case fErrorHandler(err.Number, Erl, err.Description, "VBA Document Form_AcctsOffer", "Create_Invoices_Click"): Case vbRetry: Stop: Resume 0: Case vbIgnore: Resume Next: End Select
End Sub

Question Access 2007 Database Keeps Closing

Please help how to calculate order detail

Hi Everyone,

I am sorry if my post has been cover so many times. I really appreciate for helping me with my problem.

The problem that I am having is how to recalculate all order detail item. Sorry not sure how to explain
FORM
Main form = Tblorder : orderID, CustumerID, TotalSquare
Sub form = TblOrderdetail : orderdetailID, OrderID, itemname, unitprice, total

Example: if I set up the totalsquare FIRST = 10 and I select the the itemname, it will calculate the total = unitprice * totalsquare this work fine.

The problem:
I have 20 item in sub form orderdetail and every item was calculate based on totalsquare = 10. For some reason, I have to change the totalsquare = 20.
How do I make so that it will recalculate all 20 items in subform/orderdetail instead of deleting all item and re enter it again?

Sorry if my question wasn't clear and thank you for your help!

Please help how to calculate order detail

Question NOT queries?

Hi all -

New to Access and SQL but was a pro with Paradox.

Was given assignment to create Excel spreadsheet showing which employees had training overdue. I though this would be better for Access and here I am two months later.

My issue:

I have my tables, queries and forms all set up to return employee information (add, change), Operating Procedure information (add, change), and training information (received and due).

My problem is creating a query that will return employees that require training.

The way it's currently set up is if an employee is trained in one procedure, ALL procedures that employee is responsible for also are assigned. I've attached a spreadsheet showing all 5 procedures were assigned the same training date when in reality, only the highlighted yellow was trained.

Is there a way to create a query that states I want everyone in the Employee table with every Operating Procedure assigned to them less what Operating Procedure they were trained on?

The relationship is currently setup where the employee table is a one-to-many relationship with training table.

Any help or advice would be greatly appreciated!

Thanks advance,
BBSky

Attached Files

Training Example.xls (24.5 KB)

Question NOT queries?

listbox/ combobox on a subform in Access

I am having trouble to link a listbox on a subform (table) tot de main form (table). I would like to use it as an lookup list for the connected records. Already tried a lot but but it doesn't work :banghead:. Hope it makes sense.

Attachment zipped db: the main form = 1frmjongere and de the subform =1subfrmrapportage.


Sincerely.

Attached Files

20150313dbSF_v1.0.zip (62.5 KB)

listbox/ combobox on a subform in Access

Hi all

Newbie from America - New Jersey to be exact.

Was a wiz with Paradox and reluctantly moved to Access but only dabbled in it about 15 years ago.

Only exposure to SQL was through Metaphor.

So, in short, I'm not a programmer. But I do want to thank all of the programmers here who have responded to posts that have helped me tremendously in the past few months!

BBSky

Hi all

Greetings from Holland

Hello members of this forum, I'm glad I found you ! I am a complete novice to Access programming (it's a hobby). As a social worker this is not my expertise :), bit still I am enjoying myself very much programming for a small non commercial organisation.

Greetings from Holland.

Greetings from Holland

Combo Box Issue

Please help!

I have this combo Box and use this code to populate to other fields:
Private Sub ServiceCode_Exit(Cancel As Integer)
Dim varSerC, varDiag, varRate As Variant
varSerC = DLookup("ServiceCode", "Service List", "ServiceCode = [ServiceCode] ")
varDiag = DLookup("Diagnosis", "Service List", "ServiceCode = [ServiceCode] ") & " - " & DLookup("ServiceTitle", "Service List", "ServiceCode = [ServiceCode] ")
varRate = DLookup("Rate", "Service List", "ServiceCode = [ServiceCode] ")
If (Not IsNull(varSerC)) Then Me![ServiceCode] = varSerC
If (Not IsNull(varDiag)) Then Me![Diagnosis] = varDiag
If (Not IsNull(varRate)) Then Me![Rate] = varRate

End Sub


But when the form is opened and the the ServiceCode is picked from the Combo Box, the first record is always selected and populated to the other fields. Whereas, the SerID populates correctly.

Where am I going wrong? Please look me up.

Attached Images

Access-Service.png (21.8 KB)

Combo Box Issue

send OpenArgs as Array ?

Is it possible to send several form's OpnArg as Array ?
Now I'm using a long text string including || as divider so I can split them later into Array.

send OpenArgs as Array ?

VBA codes with repeated rows in report

Hi!
I have used

Code:

---

If  Len(Result.Value) > 7 then
Result.TopMargin = 0
End If

---

vba code but it is applying to all rows whenever one of rows located in repeated area of report make condition true. How to solve this issue.

VBA codes with repeated rows in report

Recommendations: database design and tools

Can anyone recommend one or two books (or resources online) that deal with database design / modeling that:

• don't cost an arm and a leg
• don't go step by step building a database design
• easy to follow
• have tips and tricks for how to develop entities and relationships

Also, can anyone recommend free or low cost downloadable software that can be used for designing and documenting the database and display the ERD? It does not need to generate code. I am primarily looking for a tool that could help me with a volunteer database project I am working on.


Thanks,


Tom

Recommendations: database design and tools

Page Footer in Access Report

Pease help me. I was able to create only 3 pages in access report. I am not able to go beyond 3 pages since the page footer is not moving down, means I am not able to drag it down.

Am I doing something wrong here? It is a simple report of resume with only text boxes. My report comes around 10 pages.

Thanks for your help.

Page Footer in Access Report

Create a Join using LEFT string manipulation

Hi

I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.

I have two tables

Table1 will have data in a column that is 9 characters long
ULCABC123
ULCABC124
ULCABC125
PLTABC123
PLTABC124

Table2 will have data in a column that is 6 characters long
ULCABC
PLTABC

Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1


I was thinking something like this. (but then I can't enter design mode)
Query1 - Test

Code:

---

SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6)
GROUP BY  Table2.ORDDETTYPE, Table2.DESCRIPTION;

---

I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.

Query2

Code:

---

SELECT Left([ORDERDET],6) AS NEWORDDET
FROM Table1;

---

Query3

Code:

---

SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

---

Hope this makes sense.

Create a Join using LEFT string manipulation

Auto resize memo field in subform

Hello,

I have searched endlessly to see if this is possible... I have a subform that I use in a few different ways on my database. It has a comment log with a memo field for people to insert their comments. The struggle is some can be long some can be short. Is there any way to have each record height adjust to the length of the memo?

If it cannot be done for the subform in regular use, my main concern is having it formatted for reports that are generated for clients.

I clipped and attached an example from a report. Its not hte best example but the extra column height is adding additional pages to my report to accommodate one long comment.

What are my options? BTW, I am fairly new to VBA / MS access

Thanks

Attached Images

Capture.PNG (54.2 KB)

Auto resize memo field in subform

Suddenly Stopped Working

So I have code that uses a switch to open a specific switchboard depending on the department. It was working just fine. Then when I went to try adding code for the "On Load" on my log in form, it decided to stop working. I was trying to add code so that when the login form opens everything is hidden, and then when the admin logs in everything is shown again.

Here is the code for the switchboards:
Sub Security(SecurityLevel As Integer)
Select Case SecurityLevel
Case 1 'Admin Level
Public Sub UnHideNavPane()
DoCmd.SelectObject acTable, "MSysObjects", True
End Sub

DoCmd.ShowToolbar "Ribbon", acToolbarYes

Case 2 'Pastor Level
DoCmd.OpenForm "Pastor_Switchboard"
Forms![Pastor_Switchboard]![txtLogin] = TempLoginID
Forms![Pastor_Switchboard]![txtUser] = WorkerName

Case 3 'Gues Level
DoCmd.OpenForm "Guest_Switchboard"
Forms![Guest_Switchboard]![txtLogin] = TempLoginID
Forms![Guest_Switchboard]![txtUser] = WorkerName

Case 4 'Children's Ministry Level
DoCmd.OpenForm "Children's_Switchboard"
Forms![Children's_Switchboard]![txtLogin] = TempLoginID
Forms![Children's_Switchboard]![txtUser] = WorkerName

Case 5 'Food Pantry Level
DoCmd.OpenForm "Pantry_Switchboard"
Forms![Pantry_Switchboard]![txtLogin] = TempLoginID
Forms![Pantry_Switchboard]![txtUser] = WorkerName

Case 6 'Music Ministry Level
DoCmd.OpenForm "Music_Switchboard"
Forms![Music_Switchboard]![txtLogin] = TempLoginID
Forms![Music_Switchboard]![txtUser] = WorkerName

Case 7 'Nursery Level
DoCmd.OpenForm "Nursery_Switchboard"
Forms![Nursery_Switchboard]![txtLogin] = TempLoginID
Forms![Nursery_Switchboard]![txtUser] = WorkerName

Case 8 'Sack Lunch Saturday Level
DoCmd.OpenForm "SackLunchSat_Switchboard"
Forms![SackLunchSat_Switchboard]![txtLogin] = TempLoginID
Forms![SackLunchSat_Switchboard]![txtUser] = WorkerName

Case 9 'Secretary Level
DoCmd.OpenForm "Secretary_Switchboard"
Forms![Secretary_Switchboard]![txtLogin] = TempLoginID
Forms![Secretary_Switchboard]![txtUser] = WorkerName

Case 10 'Youth Ministry Level
DoCmd.OpenForm "Youth_Switchboard"
Forms![Youth_Switchboard]![txtLogin] = TempLoginID
Forms![Youth_Switchboard]![txtUser] = WorkerName


Case 11 'Sunday School Level
DoCmd.OpenForm "SS_Switchboard"
Forms![SS_Switchboard]![txtLogin] = TempLoginID
Forms![SS_Switchboard]![txtUser] = WorkerName

Case 12 'Home Group Level
DoCmd.OpenForm "HomeGroup_Switchboard"
Forms![HomeGroup_Switchboard]![txtLogin] = TempLoginID
Forms![HomeGroup_Switchboard]![txtUser] = WorkerName

Case 13 'Committees Level
DoCmd.OpenForm "Committees_Switchboard"
Forms![Committees_Switchboard]![txtLogin] = TempLoginID
Forms![Committees_Switchboard]![txtUser] = WorkerName


End Select
End Sub

I keep getting a "expecting an end sub" error, but there is an end sub...

Here's the code I was playing with that somehow messed this up:

Private Sub Form_Load()
Public Function HideNavPane() As Byte
DoCmd.SelectObject acTable, "MSysObjects", True
DoCmd.RunCommand acCmdWindowHide
End Function

DoCmd.ShowToolbar "Ribbon", acToolbarNo

End Function

Suddenly Stopped Working

2 different outcome using the same bit of code?

Hi All,

I'm changing a record source on a form when the user clicks a "save" button.

I'm doing this to store a value from the current form in a table that is not part of the default form query.

This works fine.

I have a separate button on my form called "Home", when the user clicks this button it requerys the current form which triggers the before update event to run, this in turn brings up a message box which asks the user if they want to save or not. If they click yes then it runs the SaveButton click() code.

For some reason when the user presses the save button and then exits, everything works but if a user presses the “home” button which triggers the save button then it brings up the “2107 The Value you entered doesn’t meet the validation rule defined for the field or control.” Runtime error and stops on the change recordsource command.

I’ve no idea why triggering the same code directly from a button or indirectly from a before update event has two different outcomes.

Anyone know what’s going on?

Cheers

Steve

2 different outcome using the same bit of code?

Question Automatic Checkbox???

Hi guys,

I am new to access, I still consider myself a newbie. Please if anybody posts anything try to dumb it down a little for me :D

So I have a quick database that I have made please see attachment.

On the CheckboxF form I would like Check 1,2,3,4,5,6 to be checked manually but when all of these are checked true. I would like the "JobComplete" checkbox to automatically check to true, I have bound this checkbox as I understand that I can not have an unbound checkbox on a continuous form.

I have also heard from others that it is never a good idea to automatically generate data but with no explanation if someone could explain this?

I feel that in this situation, human error of not marking "JobComplete" as true when all boxes are checked is a bigger risk.

I am open to any ideas though.

Thank you in advance. :)

Attached Files

SampleComboDB.accdb (608.0 KB)

Question Automatic Checkbox???

Crosstab Query Question

I have two fields:

1) DueDate
2) JobType

I want to create a query or report with Month&Year in the left hand row and JobType in the column header with a count of job types under the headers

Example:
PPM Electrical PPM HVAC PPM Fire Alarm
Apr-2015 15 5 15
May-2015 20 10 6
Jun-2015 21 7 12

I guess I'm looking for a crosstab query but I cannot figure out how to group the date by month in the query? Can anyone explain how to do this in a query or report? It would also be helpful if I could show the results in a bar chart.

I have attached a screenshots of where I am at.

Thanks, David

Attached Images

	CrosstabQuery1.JPG (61.0 KB)
	CrosstabqueryResults.jpg (92.6 KB)

Crosstab Query Question

Ribbon hiding!

I'm trying to 'lock' down a lot of 'features' so that the user doesn't inadvertently cause any problems.
As part of that I have hidden the ribbon using DoCmd.ShowToolbar "Ribbon", acToolbarNo when I load the first form.

For reports on the Open event I use DoCmd.ShowToolbar "Ribbon", acToolbarYes to enable the ribbon and on close I again use DoCmd.ShowToolbar "Ribbon", acToolbarNo to disable.

Everything is fine except when eenabling the ribbon I'm getting the "file" tab as well which allows user to go into options an undo a lot of the security. Can I hide this at all?

Ribbon hiding!

Help with Normalization

So I've posted a few forums on here and recently I've been told to normalize my data (which is a new concept to me). When I first started reading about it, I thought I had done a fairly good job of it (I have a table for my church members, visitors, children, those who have passed away, and those who have moved). But the more I read the less confident I am in my data sorting. Each table has a lot of fields (any and all data that goes along with that person). I was hoping someone would have the time to look at my fields (pictured) and let me know the best way to normalize my data (or at bare minimum start me on the right track so I can figure the rest out on my own).

I really appreciate the help! I'm still learning and having help and explanation on what/why I'm doing (this) helps me retain better. Apparently in my BCIS classes they don't really teach normalization or don't explain that's what we're doing.

Attached Images

	fields - part 1.JPG (33.0 KB)
	fields - part 2.JPG (32.3 KB)
	fields - part 3.JPG (34.4 KB)
	fields - part 4.JPG (35.5 KB)
	fields - part 5.JPG (35.0 KB)

Help with Normalization

Just a quick survery on best way to do a directory

Hello,
I have been working on recreating my church's records from Alpha to Access. Today I wanted to work on the directory, and I'm not sure the best way to go about this. Our old directory is set up by family, with a family picture, address, anniversary, each member's name, birthday, and phone. Then we have a page that recognizes the staff, a page for the deacons, a page for those who do not have a picture, a page for corrections/new members, a page with the weekly schedule, a page for those who have moved away, and a page for those who have passed on. I wanted to add a couple pages for pictures from the previous year's events (we print this every year). Now the pages I'm sure I can just setup manually, but for the families how's the best way to go about this? I was thinking make a table with each family name and the family picture attach, but not sure how to do the members of each family.

Thank you in advance for your help, and let me know if you need more information.

Just a quick survery on best way to do a directory

Run-time error 424, Object required

Dear people,

I hope that you can help me with my problem. I have made a code (see below) to run a code for all subforms. But I get always "Run-time error 424, Object required". I don't know why this code can find no one of all subforms? I thought that if I change this "With SubForm(Sequence)" into "With "." & SubForm(Sequence)", this problem is then going. But no result... :confused:

Code:

---

Private Sub Form_Current()
    Dim DLookUpSID As String
    Dim SubForm As Variant
    Dim Sequence As Long
        DLookUpSID = DLookup("Security_ID", "tbl_Security_Level", "[Security_ID]=" & _
                    Nz(DLookup("User_Security_ID", "tbl_Users", "[User_Name]='" & VBA.Environ("USERNAME") & "'"), "6") & "")
        SubForm = Array( _
                ".fsub_Bestellingen", _
                ".fsub_Uitvullen", _
                ".fsub_Titratie", _
                ".fsub_Verlopen_Antilichamen", _
                ".fsub_Status_Bestellingen", _
                ".fsub_Panels")
            With Me
                If DLookUpSID <= 2 Then
                    For Sequence = 0 To 3
                      With SubForm(Sequence)
                            .Locked = False
                            .Form.cmd_Add.Enabled = True
                            .Form.cmd_Undo.Enabled = True
                            .Form.cmd_Remove.Enabled = True
                            .Form.cmd_Save.Enabled = True
                        End With
                    Next
                Else
                    For Sequence = 0 To 3
                      With SubForm(Sequence)
                            .Locked = true
                            .Form.cmd_Add.Enabled = False
                            .Form.cmd_Undo.Enabled = False
                            .Form.cmd_Remove.Enabled = False
                            .Form.cmd_Save.Enabled = False
                        End With
                    Next
                End If
            End With

---

With kindly regards! Wappervliegje! :D

Run-time error 424, Object required

close access application aftera specific time

Hello,

i have an access application and i wnat that aftera specific time it would be closed automatically. I found the following code in timer event of a form that is hidden. but it takes long and i want that it works sooner than that. do you know where should i change the time?

i also have one other question. some pdf files will be opened from hyperlink of my app and when the app will be closed the pdf files are still open, is there a way that i can close all of the pdfs as well?

thank you for your help

Code:

---

Private Sub Form_Timer()
' IDLEMINUTES determines how much idle time to wait for before
        ' running the IdleTimeDetected subroutine.
        Const IDLEMINUTES = 1
        Static PrevControlName As String
        Static PrevFormName As String
        Static ExpiredTime
        Dim ActiveFormName As String
        Dim ActiveControlName As String
        Dim ExpiredMinutes
        On Error Resume Next
       
        ' Get the active form and control name.
        ActiveFormName = Screen.ActiveForm.Name
        If Err Then
            ActiveFormName = "No Active Form"
            Err = 0
        End If
        ActiveControlName = Screen.ActiveControl.Name
            If Err Then
            ActiveControlName = "No Active Control"
            Err = 0
        End If
        ' Record the current active names and reset ExpiredTime if:
        '    1. They have not been recorded yet (code is running
        '      for the first time).
        '    2. The previous names are different than the current ones
        '      (the user has done something different during the timer
        '        interval).
        If (PrevControlName = "") Or (PrevFormName = "") Or (ActiveFormName <> PrevFormName) Or (ActiveControlName <> PrevControlName) Then
            PrevControlName = ActiveControlName
            PrevFormName = ActiveFormName
          ExpiredTime = 0
        Else
            ' ...otherwise the user was idle during the time interval, so
            ' increment the total expired time.
          ExpiredTime = ExpiredTime + Me.TimerInterval
        End If
        ' Does the total expired time exceed the IDLEMINUTES?
        ExpiredMinutes = (ExpiredTime / 1000) / 60
        If ExpiredMinutes >= IDLEMINUTES Then
            ' ...if so, then reset the expired time to zero...
            ExpiredTime = 0
            '...and call the IdleTimeDetected subroutine.
           
            IdleTimeDetected ExpiredMinutes
          End If
       
       
        'Dim dtTime As Date
'dtTime = TimeValue(Time)
'If dtTime >= #8:20:00 AM# Then
    'Application.Quit
'End If

End Sub

---

close access application aftera specific time

Intro

New to the forum, advocate about access to make my job easier

Intro

Finding records in sub-forms

Hi,

I am working on MS Access 2010 and have come across a bit complex situation. I have a table with about 20 fields. In a main form, I have added 4 sub-forms where each sub-form is showing 5 fields of each record from the table. This is because of step by step data entry into the same record. I have made these sub-forms as “Continuous Forms” and have given them look of Data Sheet.

There are about 1000 records currently present in the table and these are showing in my sub-forms (5 fields each). Now the problem is that, when I have my Focus on one record in my first sub-form to enter some data, and then I click on second tab of sub-form, the same records should be shown in second sub-form as well. Currently it is not being happening and I have to scroll down my second sub-form to search that particular record.It will become much more difficult when number of records will increase.


Can anyone guide me how can I work it around and get my cursor on the same record on every sub-form? Your help shall be highly appreciated.

Regards

Finding records in sub-forms

Text box value base on ListBox

Hello All

I have 2 form ( Form A & Form B )
Form A contend Contain ListBox
Form B contend Contain bound Text box

I need to know how when double click ListBox at Form A passing value of listbox.Column(0) to bound Text box at Form B

Please at expiration builder if can

Thanks ...

Text box value base on ListBox

Costum Sort order in Subform

Hi

i have a subform with year and month
subform shows result of a select query ,
when i open query seperatly it show data as requred order but when i show data in subform releted to this query , data show in rearrange order

how can i set order in subform as query reslut

Costum Sort order in Subform

HELP! AddWorkingDays Function

Hi All,

I'm wondering if you could possible help me with my AddWorkingDays function. The issue is that for some reason Access is not recognising 04/05/2015 in my bank holidays table. I'm guessing it is something to do with format of the date, but i have so far come up short with any potential solution.

The function that i am using currently looks like this:

Public Function AddWorkingDays(StartDate As Date, numworkdays As Integer) As Date
'................................................. ...................
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim EndDate As Date
Dim workdayscount As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM TBL_BankHolidays2 WHERE [HolidayDate] > Date()", dbOpenSnapshot)
workdayscount = 0
EndDate = StartDate
Do While workdayscount < numworkdays
EndDate = EndDate + 1
rst.FindFirst "[HolidayDate] = #" & EndDate & "#"
If rst.NoMatch And Weekday(EndDate) <> vbSunday And Weekday(EndDate) <> vbSaturday Then
workdayscount = workdayscount + 1
End If
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
AddWorkingDays = EndDate
End Function

There is then another function that calls this procedure to determine the amount of days added depending on what catergory is selected on the form. A snippet of the code is below:

Select Case frm![Fuel]

Case 2
Select Case frm![Category]

Case 1

numdays = 0
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for First Visit] = targetdt

numdays = 5
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for Completion] = targetdt

Case 2

numdays = 5
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for First Visit] = targetdt

numdays = 15
targetdt = AddWorkingDays(Date, numdays)
frm![SLA Date for Completion] = targetdt

If i was to use this today and case 2 was selected then the 'Date for First Visit' should be set to 06/05/2015 but for some reason it is set to 05/05/2015.

What confuses me even more is that if i create an imaginary holiday for 29/04/2015 then the 'Date for First Visit' is set 06/05/2015 which is correct as it is 5 working days (excluding today).

Again, if i create an imaginary holiday in the table for 14/05/2015 then it sets the 'SLA Date for Completion' to 20/05/2015, which is indeed 15 working days.

I'm probably doing or missing something stupid, so any help would be much appreicated.

Many thanks,
Phil

HELP! AddWorkingDays Function