Entity Relationships Tutorial

First a bit of background, the last time I created a database was with a program I believe was called Lotus and that was 30 plus years ago. I am a self taught computer flunky, mostly dealing with websites but have my fingers in everything. But when it comes to Access I am a newbie!

I did a bit of reading and found the entity relationship tutorial and started working on that because I know the database I am building is all screwed up.

So using the tips in E/R Tutorial:
I am building a Deer Hunting Club database. We are what is called a Quality Deer Management club which means we have specific harvest targets and rules, we work on habitat improvement as well. To track our progress we collect a lot of data that I would like to use a database to help with analysis.

We need to track Bucks, Does, Fawns and unknowns, along with Hunters, Food Plots, Stands and Harvest Info.

For Bucks only (we only need to collect numbers for does, fawns, unknowns) we will collect age, antler points, antler spread, beam length, comments, active/inactive

For Hunters we need to track Last and First Names, phone, address, total dues, amount paid, date paid.

For food plots we need to track Location, size, crop and soil test

For stands we need to track Location, type, owner (hunter)

We also want track deer observations, this data will include Hunter, Date, Time, Location (Stand or Food Plot), Bucks, Does, Fawns, Unknowns, weather, temp, wind, baro press, moon phase and comments

We also want to track deer harvest with data that will be identical to Observations.

I am attaching a copy of where I am diagramming this out as shown in the tutorial, but I got stumped on a few things. I plan on using *somethingID as my primary keys and didn't want to get wrapped around doing multi-field keys etc.

I could NOT figure a relationship between Buck and Location (Stand or Food Plot), the only real world relationship occurs through the Observation or Harvest which will specify Location (Food Plot or Stand). The whole Location, Stand, Food Plot thing has me all confused, I will use Lat/Long for all three of them and they CAN be separate entities, BUT I could also make Stand and Food Plot as children of Location.

So I am trying to figure this out but gonna need a bit of help.

Thanks in advance for any help, I really hope this isn't over my head.

Attached Files

BnB Entity Relationship Diagram.zip (39.0 KB)

Entity Relationships Tutorial

Howdy Everyone

My name is Tim and I am from Nesbit, MS. Computers are one of my many hobbies to include boating, hunting anything with the outdoors. Hopefully I can learn a little bit about Access since it has been years since I used, I believe it was called Lotus...?

Howdy Everyone

Hide database

I am trying to hide my database and this script works well.

But then in the visible database I want to be able to click a button and make the other database visible, but I am not sure how to specify which database to make hidden or visible.

Code:

---

Declare PtrSafe Function ShowWindow Lib "User32" (ByVal hwnd As LongPtr, ByVal nCmdShow As LongPtr) As LongPtr

Sub test77()
HideAccess (False) 'change to true to show
End Sub

Function HideAccess(Hide As Boolean) '*** 0=Hide, 3=maximize
Dim AccessWnd As LongPtr, What As Integer
What = IIf(Hide, 0, 3)
AccessWnd = Application.hWndAccessApp
Call ShowWindow(AccessWnd, What)
End Function

---

Hide database

Question macro to manipulate unstructured data like Excel does

Hi all,
I have some tables need to be imported to Access, which is not well structured. For example:

report
4/05/2014
from SQL server
Name ID Add.....
aaa 111
bbb 222


So after imported data, the table needs to process a bit to turn it into a 'Database Table', which is pretty easy to do it in Excel via macro. But I'm not sure how to get it done in Access, please advise, thanks in advance:)

Question macro to manipulate unstructured data like Excel does

Multiple Data sets on chart

Hi All,

I have a simple line chart plotting price against date.

I would like to plot a secondary line on this chart from an array of data that I calculate. I've searched hi and low but can't seem to find a way of doing this.

The closest I've found is from this:-

http://ift.tt/1EW2QLQ
(sorry not enough posts for a link )

I can create my array of data but I can't seem to get it to work and think it may be for pivot charts....which access 2013 can't do anymore.

Can anyone give me some idea as to how to plot multiple data sets on the same graph in Access 2013.
Thanks.
El-d

Multiple Data sets on chart

code for OnClick zoom a image

Hi Everyone,

I am new to VBA for access. I am working on a form as a user interface. I have added an picture to the form. It looks small, and not clear because of the size, and each image has different size. I would like to know if there is a VBA code that will allow me to click on it, and then it will open in another window so it is bigger with the right sizes.
I attempted like this:

Code:

---

DoCmd.OpenForm "Prova", , , "Campo1=" & Campo1

---

but don't work
Prova is another form and Campo1 is the field with the picture.

I also need to save about 120,000 images.
I wonder if the best solution is to use the attachment for the field type or another.
What format should I Whereas in the mask I preview. And if Acces internally saves the images or not

Thanks in advance, any help or suggestion would be very appreciatted.

code for OnClick zoom a image

Hyperlink to tabbed form..

I've set hyperlinks to a couple of forms using the hyperlink address in properties. All works fine, and I've done it this way because the cursor changes to pointy hand when hovering over.

However, I need to navigate to a tabbed form in a main form.

Is there a way of doing this? I've been searching all sorts, and the most probable is to concatenate the destination?

description##Page1#page2... or something like that?

Is this correct?

What ever I've tried doesn't work, this includes ...

adding the main form destination to Hyperlink Address, and tabbed form in the SubAddress (Can't find main form)

Using the hyperlink builder. (Can't find anything)

Objects in this database (Only one form)

Google offers plenty of ways to do everything else but this.:mad:

As always.. appreciate your help.

Hyperlink to tabbed form..

Citrix - Spawning a IE or Exe directly on client system

http://ift.tt/1EOWRas

Does anyone have experience with this?
My goal is to have the Access form allow the user to open up an internal company app - a map app - using the key ID from the Access form.
Access runs on a Citrix session.

Citrix - Spawning a IE or Exe directly on client system

accidently entering the same value into combo box

Hi all,
I split and implemented my equipment check in check out database probibly before i did enough testing. The problem is I am finding problems and the reasons for them. I made a query to track the movement in and out of the warehouse of our equipment BUT, the end user is able to scan (accidentally) the same barcode number in the combo box during the same transaction. It would be great to use a marco or code to have the second instance of the barcode number open a message box and not allow the second entry saying " This was already scanned in" I am not strong with vba but I see the need to learn. Any help is always very much appreciated by you guys gals.
Thank you and have a great day!:)

accidently entering the same value into combo box

Append Query depending on check box

Hi Folks,

I'm after a piece of code which works as described below.

I have a save button on a form
I have a append query ready to run.

When the save button is clicked, I want the code to see if a checkbox is true or not and if its true I want the append query to run if its not then I want the form to save and nothing else. Anyone able to help??

Thanks in advance

Daniel

Append Query depending on check box

Descending Comments in Memo Field

Hello All:

Having some trouble with a memo field. For each record, the notes field is present on the Form.... I added an unbound text box (txtMemoAdd) and a command button (Add New Note). When the button is selected, it adds the note to the Read Only Notes Field and adds a timestamp using the following code:

Me.Notes = Me.Notes & vbCrLf & Now () & VbCrLf & Me.txtMemoAdd
Me.txtMemoAdd = ""

The note is added to the bottom, and I was wondering if there was a way to make the new note go to the TOP of the field (Descending Order rather than Ascending).

Many Thanks!

Shellsbells :o

Descending Comments in Memo Field

Internet 'rationing' needed as UK consuming at least 8 per cent of Britain's power ou

Internet:
A run-away power consuming hog should be rationed?
An outlandish statement since it saves more than it consumes?
Just a new justification for tax?

http://ift.tt/1QjqLJ3

The Internet is already consuming at least 8 per cent of Britain's power output, equivalent to the output of three nuclear power stations, and demand is soaring
Internet access may soon need to be rationed or restricted because the UK’s power supply
The Internet is already consuming at least 8 per cent of Britain’s power output, with the energy demand from data transmission and storage as well as smartphones, laptops and televisions. Demand doubles every four years, according to one estimate.
“We cannot make all that extra power, so we will have to restrict or reduce access, perhaps by metering consumers so they pay for what they use."

----------------------------------------------------------
Just great! As an American, whenI flew to London and took a hot shower in the hotel after landing, the staff informed me that I personally used up all of the hot water allocation for the entire Iles for three months.

In the US, we just make more energy. Is rationing really the answer?
Will the Access-Programmers site be limited to one question / anser a day? ;)

Is the London Telegraph on target with this story? Or, as the name implies, are we going back to the Telegraph? Please Moris me your answer:
-- .- -.- . / -- --- .-. . / . -. . .-. --. -.--

Internet 'rationing' needed as UK consuming at least 8 per cent of Britain's power ou

Count active members grouped by month

Hi,

I am trying to get active member count by month in MS Access.

Table looks like this:

admin ID firstName sDate eDate 1 Adam 01/01/2015 01/04/2015 2 Ben 01/02/2015 15/04/2015 3 Carl 01/02/2015 13/03/2015 4 Don 13/01/2015

Required output would be like this

2015-Jan: 2
2015-Feb: 4
2015-Mar: 3
2015-Apr: 3
2015-May:1
etc..

I got this far:
SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month]
FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null)
GROUP BY year(dimDate.Date), month(dimDate.Date)
ORDER BY year(dimDate.Date), month(dimDate.Date)


Query4 mCount Year Month 31 2015 1 71 2015 2 44 2015 3 13 2015 4

any help appreciated greatly :)

Count active members grouped by month

Count active members grouped by month

Hi,

I am trying to get active member count by month in MS Access.

Table looks like this:

admin ID firstName sDate eDate 1 Adam 01/01/2015 01/04/2015 2 Ben 01/02/2015 15/04/2015 3 Carl 01/02/2015 13/03/2015 4 Don 13/01/2015

Required output would be like this

2015-Jan: 2
2015-Feb: 4
2015-Mar: 3
2015-Apr: 3
2015-May:1
etc..

I got this far:
SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month]
FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null)
GROUP BY year(dimDate.Date), month(dimDate.Date)
ORDER BY year(dimDate.Date), month(dimDate.Date)


Query4 mCount Year Month 31 2015 1 71 2015 2 44 2015 3 13 2015 4

any help appreciated greatly :)

Count active members grouped by month

How to

In my Access 2013 Database I have a form for user information "frmUserInformation" (stored in tblusers) normal details are in there, last name, first name, etc. I have a related table for user submissions and other submissions both tied back to the user id in tblUsers.

All normal everyday stuff. However one field on my User information form can contain one string or multiple strings based on however I decide it to be (comma separated, semi-colon, etc). Usually you'd think normalize it and create a new table - however probably 95% of the time it would just contain one string. I'd rather not create a new table and deal with multiple table entries/deletions/joins, etc to accommodate that 5%.

End of the day I need to be able to click on a string and have it launch a form using that string's value. I know of no way to click on individual words in a text box to do this which leaves me to thinking about having VBA create a text box for each string in an array plus one blank one (for new entries).

or maybe a combobox, one line per item.

Then if/when the form is saved have them join back together and stored in the one field.

Can anyone think of any other way?

How to

OpenRecordset and ODBC linked tables

I am using the function below to check if my Linked ODBC tables are good. It works, but when it gets to tables that have a lot of columns, it takes about 5 seconds to return. I am checking about 56 tables and it could take up to 10 - 15 seconds. I tried using "Select 1 from tablename" and also tried "Select top 1 fieldname from tablename" as the source in Openrecordset() but it took the same amount of time. How could I speed this up ?

Function IsODBCConnected(TableName As String) As Boolean
If Not TableExists(TableName) Then Exit Function

Dim rst As DAO.Recordset

On Error Resume Next
Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
IsODBCConnected = (err.Number <> 3151)
End Function

OpenRecordset and ODBC linked tables

Create a New Record on One to Many custom form

Hi All,

I have 2 tables that have a one to many relationship between them.

Table 1 is the details table (this information never needs to change) and Table 2 is the records table (a new record will be created every year).

So for example:

TABLE 1
ID ¦ Item ¦ Brand ¦ Units ¦ Model ¦ Serial
1 ¦ Thermometer ¦ DegC ¦ TempCompany ¦ A100 ¦ 123ABC
2 ¦ Hygrometer ¦ Rh% ¦ WaterCompany ¦ B100 ¦ 456DEG

TABLE 2

RecID ¦ RecDate ¦ Measurement ¦ UniqueNumber
1 ¦ 05/05/14 ¦ 25.3 ¦ 4
2 ¦ 05/05/14 ¦ 40.5 ¦ 3
1 ¦ 05/05/13 ¦ 22.1 ¦ 2
1 ¦ 05/05/12 ¦ 23.4 ¦ 1

This works.

But what I want now is to make a form so that the user can add a new record in TABLE 2 based on the ID number in Table 1.

So as an example:

click a combo box on a form that only selects either ID 1 or 2 from TABLE 1 and then makes a new record in TABLE 2 so the user can add this year's measurements.

I can't work out how to do this.

Any ideas?

Create a New Record on One to Many custom form

Pop-Up Calendar

Is there a way to have a pop-up calendar with only a certain day of the week showing- such as all the Fridays only in the month?

Pop-Up Calendar

Mystery Parameter Request

I hope someone can help me solve this mystery. I have the below code behind a form so that a combo box will display a specific list of items based on the data in another combo box on my form.

I have two copies of this same form for two different departments. One of the forms works like a dream. However, when I copy that form, change the name, and update the code as pictured below, the form is asking for a parameter FROM MY ORIGINAL FORM and will not requery the combo box. I can't figure out why...there is no reference to the original form in my VBA as you can see below. I tried deleting the form and re-creating it, I tried deleting the code and re-typing it to no avail. Any ideas on what may be causing this? I'm at my wits' end. :banghead:

Private Sub cmboType_AfterUpdate()
Me.cmboAction.RowSource = "SELECT tblStatusList.Status FROM tblStatusList WHERE (((tblStatusList.Department)=[forms]![frmInquiryFraud]![cmboType]));"
End Sub

Mystery Parameter Request

Octothorpes Syntax Error ?

Can't see the forest because of the trees again.
What am I missing here? I get a syntax error on the ,

PHP Code:

---

Me.NoAllowDate.DefaultValue = "#" & ("[txtday]", "tblCompany") & "#" 


---

Octothorpes Syntax Error ?

Start / Stop calculations

I want to enter start times and stop times for each process done on multiple production lines. The data to be entered includes: StartTime, EndTime, and ProcessCode (Process codes being: "producing", "down time", "lunch time", "changeover", etc.) We want to see where the time is being spent during work days. I want the end result to be reporting that shows how many hours/minutes were used on each process and each production line by date or date range; including summaries. I'm now setting up the tables and am finding after much research that date/time fields can be quite complicated when calculating. A few questions come to mind. 1) Am I correct that the StartTime field and the EndTime field data type to be date/time, and the format to be general date? 2) Can the data entry be easily done on a form without having to enter date, time for each entry? 3) How do I accommodate for a start time of 11:00 pm on 5/5 and an end time of 2:00 am on 5/6? Can anyone guide me as to where I might get some specific instruction related to this project? I am getting bleary eyed and mind numbed trying to decipher everything. Please keep in mind that I am not just a novice, but really really a novice. Thank you for your consideration!

Start / Stop calculations

Note Popup on change of data

I'm not sure if this is possible or not. But if there is anyone that knows he/she would be found here!

Is it possible to show an alert/reminder any time data in a particular field is entered or changed? It would have to show up if the data was changed in table view or query view.

Until I can write something that will store historical data for these fields, I want to make sure anyone entering information will record the old information in a separate table. Yes, I know this is not the ideal way to store the type of data; but this database has been running for 10+years and is tied in to so many aspects of the company re-writing it is just not an option ...

Thanks

Note Popup on change of data

Macro to save to a Sharepoint site

I apologize if this has been asked before.

I have an Access 2013 database that I have saved on our SharePoint site.
(http://ift.tt/1JojF1I)

The db works fine but when a user clicks and opens the db, a copy is saved on their computer to make changes.
If they just close out then those changes are not on the copy on the SharePoint site just only in the computer on their copmuter.
They have to, before they close out, manually click that yellow "Save to Sharpoint Site" button.
My question is can I write some sort of macro that automatically saves to the SharePoint site when the database is closed?
Thanks
Kathie

Macro to save to a Sharepoint site

Struggling with search form and query

I am trying to create a query that takes values from a search form and provides records. I was having issues with getting results when some boxes on the form were left blank. I found a solution to that and it worked with a small number of fields. However when I make the full form query (about 8-10 fields) and run it says the query is too complex. I wrote the sql as I could not get designer to do what I wanted. Attached is the sql that works and that which is "too complex". Any help is much appreciated.

Attached Files

good sql.doc (23.5 KB)

Struggling with search form and query

Find text on loaded html page

Hello,

I hope someone can assist me with this.

I am simply trying to find text on a Web Browser Control htm page.

Using MS Access 2003, I have a form that includes the activeX Web Browser Control. On load of the form I initialize the web like below:

Code:

---

  Dim strURL As String
   
    strURL = "http://ift.tt/1IcdXkP"
    Me.WebBrowser0.Navigate strURL
    Me.WebBrowser0.Silent = True

---

The page loads fine.

Now, also, on my form is an unbound text box I call: [txtFind] and a command button I call [cmdFind]. I want [cmdFind] to find the first occurrence of the value in [txtFind]. My code below doesn't do anything when I click the [cmdFind] button. Can someone tell me what I'm doing wrong? Thanks in advance.

Code:

---

    Public oRange As Object
    Public myfindFirst As Boolean
    Public intTextLength As Long

   
   
Private Sub cmdFind_Click()
    Dim sSearch As String
    Dim strText As String
   
    Set oRange = WebBrowser0.Document.body.createTextRange
    intTextLength = Len(oRange.Text)

    If IsNull(Me.txtFind) Then
      MsgBox "Please enter the text.", vbOKOnly + vbExclamation, "Missing Text or Word"
    Exit Sub
    End If
       
        sSearch = txtFind.Text
        Me.WebBrowser0.SetFocus
       
        If oRange.FindText(sSearch, intTextLength) Then
            oRange.SELECT
            oRange.scrollIntoView
            cmdFind.Caption = "Find Next"
            Me.Refresh
           
           
        Else
            MsgBox ("Search string " & txtFind.Text & " not found.")
        End If
       
    Else
        Me.WebBrowser0.SetFocus
        Call oRange.Move("character")
        Me.txtFind.SetFocus
        sSearch = txtFind.Text
        If oRange.FindText(sSearch, intTextLength) Then
            Me.WebBrowser0.SetFocus
            oRange.SELECT
            oRange.scrollIntoView
        Else
            MsgBox ("Finished searching Document for string " & txtFind.Text)
            cmdFind.Caption = "Find"
            Me.Refresh
        End If
    End If

---

Find text on loaded html page

Microsoft Chart Object in Report - Formatting Line Weights

I am a bit rusty. Using a Microsoft Chart Object 6.0 in an Access 2010 report. It's easy enough to do the basics and that chart responds to data.

In my case, I have 12 lines, or columns, being controlled by data. It responds to the data. just fine. What I want to do is control the line weight and colour of each line through VBA.

You can click on the chart itself on the report form, but formatting the line you actually want is almost impossible. Pretending we can, you get the pop-up configuration windows and the TAB "PATTERNS". Under that, you can select "LINE" and then choose the style, color and weight. This is what I need to do in VBA.

Lines like this do not work:

Graph_Data.Columns("A").Line.Weight = 5
or...
objDataSheet.Columns("A").Line.Weight = 5

I tried a variety of versions of that and am pulling my hair out.

I am hoping someone on here has experience formatting a chart object through VBA in Access.

Thanks kindly,

Dave

Microsoft Chart Object in Report - Formatting Line Weights

Need Help in DataMacros... Especially with LookupRecord?

Hi all. I am Prabhakaran Karuppaih - A VBA Developer doing Ms-Access and Excel Development for more than 6 years. But the physical macros (The Macros available in the Navigation pane) are an untouched subject to me till now. Yesterday when I wanted to do a Validation for a table I stumbled upon a new concept called DataMacros. There is no problem in understanding in what is that and what it does and all, but the syntax are the one little I'm confused of:

Let me tell my current requirements,

I have a Log Table having fields like Logging_Time and Logging Resource and all

and an another table called

Roles where are there 3 roles: Regular User, Admin, Super Admin.

A Regular User can enter Logs for only for the current date

but an Admin can enter Logs for upto 2 days in past (for other users too)

Finally a Super Admin can do anything.

The problem is currently the validation Rules are not alllowing to lookup the Roles Table Matching the Resource_ID, Resource_Role to get the Role_id from the Roles Table.

As I want to give only a table level validation, I know I have to use the Data Macros especially the Before_Change.

Kindly help me on How to get the Role_id from the Roles table using the LookupRecord and based on the Role_id I want to some different validations for Different Roles. All I want to do with the Data Macros only. Kindly help.

Need Help in DataMacros... Especially with LookupRecord?

MySQL Head Banger :@ !!!

Hello Follow Nerdlings :D

I'm about to bash my head :banghead: off of every surface in sight if I don't figure out what I'm missing...

This query:

Code:

---

$query = 'SELECT comments.id, comments.comment, users.first_name, users.last_name ' .
        'FROM comments left join users on users.id = comments.user_id ' .
        'WHERE comments.post_id = ' . $post_id . ' AND comments.date_deleted IS NULL ' .
        'ORDER BY comments.post_id DESC';

---

Is still returning records with date_deleted fields that have values in them... WHY!? NO ERRORS AND CORRECT SYNTAX!!!

I'm going to cry here soon lol... the little things in life they say :mad:

MySQL Head Banger :@ !!!

Export Report without highlight color

Dear Friends
I am exporting a report from access 2010 as PDF to email and the email attachment is coming highlighted. I need it to be white like the report when seen on screen with alternate rows to NO. if I export it to PDF and view it from within access it comes perfect, but when exported to an email, its coming shaded. any idea for a help? attached a sample

thanks a million

Attached Files

New Customer Delivery Form.zip (99.1 KB)

Export Report without highlight color

Question Personalised Staff Rota

I'm putting together the staff rota for the some graduation ceremonies and I want to ideally give each staff member a personalised rota showing what they are doing in each ceremony. I've currently got a table where each record is a ceremony and then each field corresponds to a particular task and is populated with a member of staff. Is there a simple way of doing this?

Question Personalised Staff Rota

Importing Excel to2013

Hi I'm a new user to Access, I’m trying to import additional data from Excel into an existing Access database. I’m using the Import Wizard for excel but receive the error message “There is no object in this control”
Where have I gone wrong?
Thanks

Importing Excel to2013

Format - Convert a weeknumber+weekday to a proper date format

Hi,
I have a query, that contains the field, weeknumber and weekday value, and year.

now i want to convert this values to proper/standard date format.

Example:
Weeknumber: 19
Weekday: 3 (Tuesday)
Year: 2015

Expected Result: May 5, 2015

Thanks in advance. :)

Format - Convert a weeknumber+weekday to a proper date format

Sql

Whats wrong with this code: Can any one solve this please:

((Table1.Location)=[Enter Project Number]) AND ((Table1.EmployeeID)=IIf([Location]="0217",[Table1].[EmployeeID],[Table1].[EmployeeID]) And (Table1.EmployeeID)<>287 And (Table1.EmployeeID)<>183 And (Table1.EmployeeID)<>264));


Its not giving me the result. May be the issue with <>. Is there any alternative method for this. Plz advise.

Regards,

Paul C:banghead:

Sql

Getting Total of a field in a continuous form

Dear Valued colleagues,
Hope each one of you are doing all well, please help me to solve a problem, let me explain my situation:
i have three tables (Group, Customer, Savings).
every member of a group do savings once every 15 days and each group has maximum 20 members, group has one to many relationship with customer and customer having one to many relationship with Savings (there is no direct relationship between group and savings) i have created a continuous form for saving entry. in this form i select a group which is unbound and based on that group, its members are listed in the combo box named CusID, after selecting customer other saving details are entered.
What i want, is that for every group i want a total saving for all that specific group's members selected in the mentioned unbound combo box in that continuous form, this total should be based on the group selected in the mentioned unbound combo box, in case i select another group the total should also be based on newly selected group

Getting Total of a field in a continuous form

Needed Professional way to link three tables using forms

I am self taught for Microsoft Access and have working with access databases for couple of years. I need to an expert way for the below scenario:

I have 2 forms, Form1 is used to enter staff personal and salary information while Form2 will be used to enter Bank information. I am using three tables to maintain this information:
1- Personal Info
2- Salary
3- Bank Details

All tables are joined in relationship by using a field StaffID which have to be entered manually as I can not use auto number. I have enabled cascade update and delete in relationships

The problem is if i use the Form1 to enter the personal & salary information, the StaffID which I enter in Form1 is automatically updated in salary table as Form1 is using both personal & Salary tables. But the StaffID is not updated in table Bank Details. This is crucial as this StaffID is a link between Personal and Bank table. Is there any way that when I enter StaffID in Form1 it will automatically append StaffID in Bank Detail table? Or can I use any query to append StaffID to Bank Details table.

Attached Files

Sample Database.accdb (896.0 KB)

Needed Professional way to link three tables using forms

Help needed for nested iif in microsoft access 2010

Hi all,

Current i have 3 table known as

Trainee- nric,name,address
Result- nric,course_code,result
Courses- course_code,count_core,count_elective,core/elective

i link Trainee.nric to Result.nric and Result.course_code to Courses.course_code

i need to show the criteria whereby the trainee must met the condition of completing 3 core module and 3 elective in order to be eligible.

im able to sumup the number by using sum(count_core) and sum(count_elective) to show that the trainee met the 7 core and 3 elective criteria.

Full qualified: IIf([Sumofcount_core]>=5 And [Sumofcount_elective]>=3,"Qualified","not qualified")

however i need to add one more condition because those that take the core/elective might fail and i might be still count them as pass so i need to add in [Result].[Result] = "COMPETENT" in my code

Would appreciate alot if someone could help me in that
Thanks. :)

Help needed for nested iif in microsoft access 2010

Presidental Tracking db

Does anybody know of a ready made template that would serve as good tracking of all the Presidential candidates, Their past deed, god amd bad, were served etc.If not, would anybody offer me ideas of what to put in it.to build our own.
D7A

Presidental Tracking db

Need help on Insert

I am new in Microsoft Access and currently facing an error. I have no idea why I get this problem. Same code works well on another system.

When i input s898688j (example) in a text box, it will give me run-time error '3061': Too few parameters. Expected 1

When i input 986343dd (example), i will give me run-time error'3075': Syntax error (missing operator) in query expression '986343dd'.

When i input 6986324 (example), the insert query is successful.

I had done all the research and i have no idea why there is error.

I have 2 tables

trainee table:

ID (autonumber),
full_name (text),
IC_num (text),
phone_no (text),
company_name (text)
course table:

course_id (autonumber),
IC_num (text),
course_name (text),
L3_survey (number),
L4_survey (number),
start_date (date/time),
end_date (date/time),
no_of_days (number)

Insert query in VBA:

For trainee:

CurrentDb.Execute "INSERT INTO trainee(IC_num, full_name, phone_no, company_name)" & _
" VALUES (" & Me.newIC & ",'" & Me.newName & "','" & _
Me.newPhone & "','" & Me.newCom & "')"

For course:

CurrentDb.Execute "INSERT INTO course(IC_num, course_name, L3_survey, L4_survey, start_date, end_date, no_of_days)" & _
" VALUES (" & Me.newIC & ",'" & Me.newCou & "','" & _
Me.newL3 & "','" & Me.newL4 & "','" & Me.newStrt & "','" & Me.newEnd & "','" & Me.newDay & "')"

I had tried a lot of ways but i have no idea why is it not working!!! :banghead:

Need help on Insert

Routers Suck

Is it just the routers in my life or are others having routers that seems to always need to be resetting? I just lost two hours of time today because the foolish router at my wife's rehab had to be reset. restaurants I go to, the one at my house same thing. We can put a man on the moon but can't seem to make decent routers.

Routers Suck

Could this really work or is this make-believe

For the past year I’ve been saving some really great VBA scrips that people have helped me put together.

Now that I’ve saved all these wonderful VBA scrips in folders as .txt files

I’m wondering if someone has been able to put together a macro that can be called from either a excel, access, outlook, or Power Point and run the VBA script. If anyone has come close or has been able to do this could you show me where to find this magical script?

If I can click one button that asked me what macro or script I would like to run would be the best Macro or VBA scrips I have found,

Could this really work or is this make-believe

A way to accurately size a pop-up form

Some time ago when I was programming in Access 2000, I used to get frustrated because I could never by sure what size a pop-up form would be and where it would appear. As a result, I used the code shown below to precisely size and position my form, then set PopUp = True; Modal = True. It did the trick and I could set the size and position using inches instead of Twips. It worked fine back then. I have needed to return to this technique, but I think Access has changed or I have forgotten the other settings because this no longer works for me. I think that maybe I have forgotten some other form settings.

Note: I realize there probably is an easier way to size and position, but this template had worked so well in the past. I am just trying to see if there is a simple explanation on why it is not working now.

Question: looking at my code (below), can anyone think of why it is not allowing me to size and position my form?


------------------------------------------------------------
In a regular module, I have the following statement:

Code:

---

Global Const gcint_Inches_to_Twips As Integer = 1440

---

At the top of the form module in the declaration area, I have the following code and I set the size and position of the form in inches:

Code:

---

Private Const mcsng_Form_Right_in_Inches As Single = 2.3
Private Const mcsng_Form_Down_in_Inches As Single = 1.5
Private Const mcsng_Form_Width_in_Inches As Single = 4.5
Private Const mcsng_Form_Height_in_Inches As Single = 2.5

---

In the Form Load event, I have the following:

Code:

---

Private Sub Form_Load()
    With DoCmd
        .MoveSize Right:=(mcsng_Form_Right_in_Inches * gcint_Inches_to_Twips)
        .MoveSize Down:=(mcsng_Form_Down_in_Inches * gcint_Inches_to_Twips)
        .MoveSize Width:=(mcsng_Form_Width_in_Inches * gcint_Inches_to_Twips)
        .MoveSize Height:=(mcsng_Form_Height_in_Inches * gcint_Inches_to_Twips)
        .Restore
    End With

End Sub

---

A way to accurately size a pop-up form

A query with Distinct against a SQL view with Distinct truncates string

Just wanted to know if anyone had heard of this or woul know why it does this.

A SQL View - has Distinct - one of the fields is a large text field.
This works fine. The Access DSN-Less linked table shows all the charaters in the text field.

On the Access side, a query against the the Linked Table had a Select Distinct *
The recordset assigned the result into a string variable.
It should have been over 530 characters - it was one word.
Removing the last two words, it then was about 212 characters (the last half of the complete field).
All the records with about 212 characters (or less) appeared to work fine.

On the Access query, removing the Distinct (just Select *) fixed the problem.

Granted, the 2nd Distinct was not required.
This just seemed like a crazy result. It wasn't as if "oh, the 2nd Distinct is causing this to happen" moment arrived.

Anyone heard of this?

DSN-Less ODBC connections using SQL Server Native Client 11.0 and DAO recordsets.

A query with Distinct against a SQL view with Distinct truncates string

Im in too far

:confused:
I working on building a query to make a report for my supervisor that will provide data on anybody who has had a problem UA (urine screen) within a specific time frame.

The query below provides me with that I need, but doesn't sort out just the problem screens, it shows everyone who provided me with a UA.
__________________________________________________
SELECT Client_Case_Information.Client_Last_Name, Client_Case_Information.Client_First_Name, Drug_History.DrugHistoryID, Drug_History.CaseID, Client_Case_Information.Case_Manager, Client_Case_Information.Status, Drug_History.Screen_Flag, IIf([Manual_Flag],IIf([Screen_Flag],"(screen)",""),"") AS ManualScreen, IIf([Manual_Flag],IIf([Screen_Flag],"","(relapse)"),"") AS ManualRelapse, IIf([Screen_Flag],"","R") AS ScreenEntry, Drug_History.Drug_Date, Drug_History.Panel, Drug_History.Time_Collected, Drug_History.CRet_Amount, Drug_History.Positive_Flag, Drug_History.Complete_Flag, Drug_History.NCDR_Flag, Drug_History.Cancel_Flag, Drug_History.Dilute_Flag, Drug_History.Late_Flag, Drug_History.Observe_Flag, Drug_History.Relapse_Flag, Drug_History.Manual_Flag, Drug_History.Comments, IIf(([Positive_Flag] Or [Late_Flag] Or [Dilute_Flag] Or [NCDR_Flag] Or [Relapse_Flag]),IIf(([Resp_Blind_Review] Or [Resp_Discharge_From_Program] Or [Resp_Education] Or [Resp_Incidental] Or [Resp_Increase_Monitoring] Or [Resp_Practice_Restrictions] Or [Resp_Practice_Restrictions] Or [Resp_Referral] Or [Resp_Reported_To_Board] Or [Resp_Treatment]),(IIf([Resp_Increase_Monitoring],"Increase Monitoring, ","")+IIf([Resp_Treatment],"Treatment, ","")+IIf([Resp_Referral],"Referral, ","")+IIf([Resp_Reported_To_Board],"Rpt to Board, ","")+IIf([Resp_Discharge_From_Program],"Disch from Program, ","")+IIf([Resp_Incidental],"Incidental, ","")+IIf([Resp_Practice_Restrictions],"Practice Res, ","")+IIf([Resp_Blind_Review],"Blind Review, ","")+IIf([Resp_Education],"Education, ","")),"** select responses **"),"") AS Response, IIf([Relapse_Flag],IIf(IsNull([Rel_Date]),"","Date: "+CStr([Rel_Date])+" ")+IIf(IsNull([Rel_Duration]),"","Duration: "+CStr([Rel_Duration])+Chr(13)+Chr(10))+IIf(IsNull([Rel_Amount]),"","Amount: "+CStr([Rel_Amount])+Chr(13)+Chr(10))+IIf(IsNull([Rel_Acquired]),"","Acquired: "+CStr([Rel_Acquired])),"") AS RelapseEntry, Drug_History.Resp_Increase_Monitoring, Drug_History.Resp_Treatment, Drug_History.Resp_Referral, Drug_History.Resp_Reported_To_Board, Drug_History.Resp_Discharge_From_Program, Drug_History.Resp_Incidental, Drug_History.Resp_Practice_Restrictions, Drug_History.Resp_Blind_Review, Drug_History.Resp_Education, Drug_History.Rel_Date, Drug_History.Rel_Duration, Drug_History.Rel_Detection, Drug_History.Rel_Reaction, Drug_History.Rel_Amount, Drug_History.Rel_Acquired
FROM Drug_History INNER JOIN Client_Case_Information ON Drug_History.CaseID = Client_Case_Information.CaseID
WHERE (((Client_Case_Information.Status)="PA and MP signed") AND ((Drug_History.Screen_Flag)=True) AND ((Drug_History.Drug_Date)>=[Forms]![Parm_Dates_Two]![txtStartDate] And (Drug_History.Drug_Date)<=[Forms]![Parm_Dates_Two]![txtEndDate]))
ORDER BY Drug_History.Drug_Date DESC , Drug_History.Time_Collected DESC;
________________________________________

Next Problem.

I do, however need to add data from one more table to get the additional information I need in the Report.

When I tried to add the table and run the query, it ends up filtering out only the Positive screens (table name is Drug_History_ Positive. Info I need from it is the Drug, Level, if they Admit or Deny.

Please help if possible.

TIA.
Sheryl

Im in too far

get records from day before after 5 PM and today before 8 AM...

I need to find records for the day before after 5 pm and today before 8 AM,

I'm using access 2010, the field type is a timestamp, (mm/dd/yyyy hh:nn:ss)

I'm doing it manually using date()-1 and date(), then eliminating the records before the day before that we received before 5 pm and eliminating the records we received after 8 AM.

Theres gotta be a code for this..

I'm new to access, I'm new to programming.... Hello all and Thank you...

get records from day before after 5 PM and today before 8 AM...

Integrative Management of Erectile Dysfunction

In the US, we have tons of TV Advertisements about this. But, perhaps it can be discussed more in detail here.
Would hope to keep this to actual Medical Professional level, not just some street-wise back alleyway advice.

Main cause: Basically age

Causes: (one short page)
http://ift.tt/1AAxODX

Cited (non-prescription) medical studies:
Emerging evidence and case reports suggest that naturally occurring agents such as L-arginine,2,23 Korean red ginseng,24 zinc,25 DHEA,26-31 maca root,2,32 and Tribulus terrestris33 may help improve sexual function and thus ED. A naturally occurring alkaloid called yohimbine, derived from the African tree, Pausinystalia yohimbe, has been used for over 70 years as a pharmacological agent in treating ED.2,34,35 Other herbs that have been reported to improve ED include horny goat weed, oat straw (Avena sativa), damiana, muira puama, and ashwagandha. Studies of these herbal plants have often yielded inconsistent results, and clinical evidence to support herbal agents in managing ED is still minimal.

March 11, 2011 - new studies
http://ift.tt/1AAxOE1
"A review published online on March 5, 2011 in the journal Food Research International reports aphrodisiac properties for several herbs while busting the myths surrounding chocolate and wine,..."
"...only the herbs ginseng, saffron and yohimbine were demonstrated to improve erection quality in human males. Alcohol, while increasing arousal in humans, has an adverse effect on performance."

With that introduction: I take ginseng, saffron and yohimbine as a daily routine. It is my opinion that they make a significant difference.

Are there any other opinions worth sharing?

Integrative Management of Erectile Dysfunction

Creating a New Record Within a Subform

Hi Folks,

After some research on this site, I was able to solve half of my problems with my current database project. However, like often time, I fixed one thing only to create a new issue. Here's what I'm trying to do:

I have a main form that controls 2 subforms. sbfActiveProjects displays a list of records that have not been flagged as complete. Upon clicking on a record in sbfActiveProjects, the details of that record show up in sbfProject. This functionality works great, except when I try to create a new record. The code works fine for getting a new record, however once I run the command I lose my ability to navigate to other active records. Any suggestions?

Code:

---

Private Sub cmdNewProject_Click()
    'set focus to sbfProject
    sbfProject.SetFocus
   
    'navigate to new record
    DoCmd.GoToRecord , "", acNewRec
End Sub

---

Creating a New Record Within a Subform

Pull data from query (or table) using VBA

I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.

I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.

Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.

How do I do this?

Pull data from query (or table) using VBA

Form objects

I am moving the columns in a subform and then running code to save the order of the column names and other properties such as order, width, hidden, DS caption,etc... When I loop thru the controls in the form. It is not capturing the order of the columns that I moved them to. Why is this not working? It works for me in another form I have.

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case 111, 106, 109 'acComboBox, acCheckBox, acTextBox
' Save Property values into a variable with a delimiter at the end.
scolname = scolname & ctl.Name & "^"
sColOrder = sColOrder & ctl.ColumnOrder & "^"
sColWidth = sColWidth & ctl.ColumnWidth & "^"
sColHidden = sColHidden & ctl.ColumnHidden & "^"
sColDSCaption = sColDSCaption & ctl.Properties("DataSheetCaption").VALUE & "^"


Case 112 'acSubform ' Subform
Call Save_AuditorView(projectID, ctl.Form)
End Select
Next ctl

Form objects

Need help with report formatting

Hello. I'm relatively new to Access (I have some very limited experience with Visual Basic .NET). I'm designing a report which has a few requirements that I cannot seem to get Access 2010 to fulfill.

The report I am going to generate will always have two pages and may or may not have a third page.

This link explains how I want the pages to appear:

Code:

---

http://ift.tt/1EamE9o

---

The reason for the unusual formatting requirement is that the first two pages will be printed on both sides of the same paper. If the report extends to page 3, that will be a separate piece of paper that will need a company logo and contact information.

I've tried hiding the header for page 2 by making access check what page it is printing and making the headersection invisible if it is page 2. But that leaves an ugly whitespace that I don't want. And apparently, the page header/footer don't have the "canshrink" property (only report header/footer do). The problem with adding the logo and contact info to report header/footer is that I don't know how to replicate the header for page 3 and footer for page 1 (since access will try to print it only at the end of the whole report).

Any help will be very much appreciated.

Need help with report formatting

Filter by Month and Year

Hello all,
I've seen a lot of similar issues to this raised, but I've not found a proper response to this question. I'd like the user to be able to search a subform only by month and year. We have lots of reports with different days, but none of them are pertinent to searching.

I don't mind if it looks a little bit messy (such as using the calendar drop down and it discounting the day) but I'd like to have it all in one box, preferably where the user can just select ex. 05/2013 and all of the reports would come up from that month.

Filter by Month and Year

Hello

My name is Ernesto.

I started to work with Access in the 90's.

I hope to learn even more with you.

Greetings from Canary Islands.

Hello

ComboBox value updates another combo box

Hi
I'm trying to update a combobox(called TypeCombo) list values after I select a value in another Combobox(called manCombo). The values for both Combobox comes from the table called EquipmentTable. Which has 3 fields Manufacturer, Type and Model.

Code:

---

 
 Private Sub ManCombo_AfterUpdate()
 Me.TypeCombo.Value = DLookup("[Type]", "EquipmentTable", "Manufacturer = " & Me.ManCombo.Value)
 End Sub

---

I know that the value I select in ManCombo is being passed into the DLookup but TypeCombo.Value always equals Null. Is this because the Dlookup returns multiple values and if so should the code be something like

Code:

---

 
 Private Sub ManCombo_AfterUpdate()
 Me.TypeCombo.List = DLookup("[Type]", "EquipmentTable", "Manufacturer = " & Me.ManCombo.Value)
 End Sub

---

Any advice would be greatly appreciated

ComboBox value updates another combo box

How to split hypen from text in Access 2010

I have been doing an exercise to split the data from the list of the information into column A, B & C. Unfortunately the data value that i have consist of several set of data format therefore it create an issue for me to separate the information in Access 2010 easily. :(

eg.

Original Data Field
FLRY-B-0.75-L-GY
FLRY-4.0-V-R
VTAC-GY 19X20
COT-F2-5-DL

Actual Data To Populate Into Column A, B & C should be the following :

A
FLRY-B
FRY
VTAC-GY
COT-F2

B
0.75
4
19X20
5

C
L-GY
V-R
(No data captured for this column)
DL

How to split hypen from text in Access 2010

Hello everyone...

Hi, I'm HC and currently working as a Data Analyst in Automotive industrial.
I have being working in Access for a couple of years however i would love to explore and gain an additional knowledge from all the ppls who are expert in Access too :D

Hello everyone...

rename command buttons from a table

Hi all

I made a Point of sale using a form with different sub-forms which have command buttons on it. They basically run a append query from tblStockMain to table cash and on to transactions. That works all fine no problem.
The one missing thing to make it sweet is to rename the command buttons automatically from tblStockMain. With other words, if I change a product in the table it would change the name on the button and the picture if possible with it.
Is there a way to do it? I don't seem to find anything suggesting it's possible but maybe.....
thanks Pete

rename command buttons from a table

Open database and switch to it

I want to open a different database from my current open database and if it is already open then switch to it.

I found a script that will open the database but if it is already open it will not switch to it.

But my real question is the following and I will get slammed for it as I'm certain it is a stupid question.

Can I open the database without the prompt to input username and password without having to remove the protection for the database.

I can store the username and password in a hidden form when the database opens, so I will always be able to know who the user is and fill tables etc... that require the username to be filled in.

I know that protection is there for a reason, but you never know unless you ask.

Open database and switch to it

script to compile database

I have been googling on how to auto compile database using code.

I found the following 2 scripts and neither work.
As per my third script I set up a script that would error upon compiling, yet neither of these scripts work.
Yet when I manually compile the database it finds the error.

Code:

---

Sub test2()
Dim strProjectPath As String, strProjectName As String
strProjectName = Application.CurrentProject.Name
strProjectPath = Application.CurrentProject.Path
SysCmd 602, strProjectPath, strProjectName 'compact DB

SysCmd 504, 16483 ' compile
End Sub

---

Next

Code:

---

Sub CompileAll()
  ' Comments  : Use menu commands to compile and optionally save modules
  ' Parameters: fSave - True to compile and save, False to only compile
  ' Returns  : nothing
  '
  Dim dbsCurrent As Database
  Dim conTmp As Container
  Dim docTmp As Document
  Dim fFound As Boolean
  Dim intOpenedType As Integer
  Dim strOpenedName As String
  Dim strName As String

  Set dbsCurrent = CurrentDb()
  fFound = False

  ' Need to open a module, so let's find one
  Set conTmp = dbsCurrent.Containers("Modules")
  If conTmp.Documents.Count > 0 Then
    DoCmd.OpenModule conTmp.Documents(0).Name
    intOpenedType = acModule
    strOpenedName = conTmp.Documents(0).Name
    fFound = True
  Else

    ' No modules, try for a form
    Set conTmp = dbsCurrent.Containers("Forms")

    ' Need to iterate to ignore lightweight forms
    For Each docTmp In conTmp.Documents
      strName = docTmp.Name
      DoCmd.OpenForm strName, acDesign, , , , acHidden
      If Forms(strName).HasModule Then
        fFound = True
        intOpenedType = acForm
        strOpenedName = strName
        Exit For
      End If
    Next docTmp

    ' No form class modules, try for a report
    If Not fFound Then
      Set conTmp = dbsCurrent.Containers("Reports")

      ' Need to iterate to ignore lightweight reports
      For Each docTmp In conTmp.Documents
        strName = docTmp.Name
        DoCmd.OpenReport strName, acDesign
        If Reports(strName).HasModule Then
          fFound = True
          intOpenedType = acReport
          strOpenedName = strName
          Exit For
        End If
      Next docTmp
    End If
  End If

  ' If the database contains at least one module, it
  ' should now be open, so lets to a Compile All{Save All}
  If fFound Then
    If fSave Then
      DoCmd.RunCommand acCmdCompileAndSaveAllModules
      MsgBox "All modules have been compiled and saved."
    Else
      DoCmd.RunCommand acCmdCompileAllModules
      MsgBox "All modules have been compiled."
    End If

    DoCmd.Close intOpenedType, strOpenedName
  Else
    Beep
    MsgBox "This project has no modules to compile."
  End If

End Sub

---

script to cause compiler to fail

Code:

---

Sub test()
lalalala
End Sub

---

script to compile database

Converter

Hi all,

A bit of a longshot me thinks! But anyway... can you make a measurement converter on Access 2013. I would like to be able to convert meters into feet and feet into meters!

Thanks

Converter

Show users loged in the database

Hi all,

I would like to create a form that will show the users connected to the database. It would be kind of a tracker who opened the database to work on some cases. The form could show all users from the datbase from top to bottom and to the right a green or red round dot that she/he is active opened the database or not active (closed the database or did not open it still)

I was thinking that it could be applied when the main form opens and that form captures the login from windows into the "tracker" form.
I guess it will only work when the "tracker" form is first open. If the "tracker" form is open later, how to capture those loged in earlier?

Any ideas or links where I could find more info?

Greetings.

Show users loged in the database

Code Burnout!

In the words of the 3 stooges, I'm trying to think but nothing happens! :D

Code Burnout!

Splitting DB Problem Please Help.

Hello Experts,

Once again need your expert advise, I split my data base and place the front end and back end at one folder. In my lap top its functioning properly, but when I copy the folder via flash drive to my Office computer, the front end won't open (.. please see image below), but the back end where the tables open.

Attachment 58332
Attachment 58333

What did I do wrong? Please help, can I still recover the forms, queries, and reports in my front end?"

Thank you very much in advance.

Cheers!
Chino

Attached Images

	Access Error.jpg (84.5 KB)
	Access Error 2.jpg (85.2 KB)

Splitting DB Problem Please Help.

Is this a design problem or SQL problem?

I have a table (TbAttendees) for recording numbers of training course participants from different organisations (different rates may be charged to each organisation) and another table (TbGroups) to allocate facilitators and other resources for each group. I have a one to many relationship from the Groups table to the Attendees table (One group consists of many attendees),
The allocation of facilitators and other resources is dependent on the total number of participants. Eg ten people from 3 organisations could be designated to a single group (eg Group A, B or C).
I have a query (QueryGroupSummary) that totals (sums) the number of participants for each group.
if I then run a query that links the totals (QueryGroupSummary) to the Group tables to allocate facilitators and other resources it’s not updateable; eg, i can't change the number of trainers). Is my relationship structure wrong or does the SQL need to change?

Is this a design problem or SQL problem?

Captions on form

Hi! I have a table composed of more than 20 fields. Each field I put captions. When I run the table, the captions work well and appear as I like. For example, the field name is LastName and the caption is Last Name. The problem is, when I make a form and set it to datasheet view, the caption does not appear. The weird thing is, some of the captions work fine, and some don't. I don't know what's wrong. What am I doing wrong here? Can anyone please help?

Captions on form

ComboBox not appearing on Subform

Hello Again (ugh)

So I have a Form (frmPositions) with a subform attached (fsubPosSkill)
They are linked through Position ID

frmPositions Record Source is qryPositionNeeds

Code:

---

SELECT tblPositionNeeds.PositionNeedsID, tblPositionNeeds.PositionID, tblPositions.Position, tblPositionNeeds.NeedsID, tblNeeds.NeedStatus
FROM tblPositions INNER JOIN (tblNeeds INNER JOIN tblPositionNeeds ON tblNeeds.NeedID = tblPositionNeeds.NeedsID) ON tblPositions.PositionID = tblPositionNeeds.PositionID;

---

It has two texts boxes on it [PositionID] and [NeedID]

fsubPosSkill Record Source is qryPositionSkills

Code:

---

SELECT tblPositionSkills.PositionSkillID, tblPositionSkills.PositionID, tblPositionSkills.SkillID, tblSkills.SkillName, tblPositionSkills.SkillValueID, tblSkillValue.SkillValue
FROM tblPositionSkills, tblSkills, tblSkillValue;

---

It has two combo boxes, cmbo1 and cmbo2.

cmbo1: control source is SkillID and Rpw Source is qrySkill
cmbo2: control source is SkillValueID and Row Source is qrySkillValue

when I open frmPositions, the subform shows only the combobox label in the header, there is no combobox that allows me to select Skill or Skill Value.

Any idea why?

ComboBox not appearing on Subform

Referential Integrity Relationships - in vba

Hi! Hope you're not still fed up with me :p.
I'd like to appeal to your knowledge.
I'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.
Looking online, I found this code:

Code:

---

Public Function CreateRelation(primaryTableName As String, _
primaryFieldName As String, foreignTableName As String,  _
foreignFieldName As String) As Boolean


    On Error GoTo ErrHandler


    Dim db As DAO.Database
    Dim newRelation As DAO.Relation
    Dim relatingField As DAO.Field
    Dim relationUniqueName As String
 
    relationUniqueName = primaryTableName + "_" + primaryFieldName + _
                        "__" + foreignTableName + "_" + foreignFieldName
 
    Set db = CurrentDb()
 
    'Arguments for CreateRelation(): any unique name,
    'primary table, related table, attributes.
    Set newRelation = db.CreateRelation(relationUniqueName, _
                            primaryTableName, foreignTableName)
    'The field from the primary table.
    Set relatingField = newRelation.CreateField(primaryFieldName)
    'Matching field from the related table.
    relatingField.ForeignName = foreignFieldName
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
    'Add the relation to the database.
    db.Relations.Append newRelation
 
    Set db = Nothing
 
    CreateRelation = True

     
Exit Function

---

Even with the comments I don't really understand what exactly this code does or doesn't do.
I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
It would be nice if someone explained to me:
- why it's not visible among other relashionships (or is it me?? :confused: maybe, it wasn't the code that was working but the query...)
- how to implement referential integrity
- what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?

If someone has a link to some easy to understand explainations, I'd gladly read them.
Keep in mind that in front of you is a novice. :) Thanks!

Referential Integrity Relationships - in vba

Calculated fields not working

Hi,
I have a problem that every few weeks the calculated fields stop working in all forms. This is solved by rebooting the server. While this solves the problem is is really inconvenient for the users and I would like to stop it from happening, or at least understand what is going on.

We have a SQL back end and 5 servers that run the front end, full version of Access on my administration server, and runtime on the 4 other servers which are paired to load balance. The problem only ever happens on the runtime version and usually on 1 server at a time (although this morning I have 2 on the same load balance so no where to move the users to).

This is not really my area of expertise but the server guys don't seem to want to investigate further and I have googled the hell out of this problem and come up with nothing. So has anyone else come across this problem?????

Cheers
H

Calculated fields not working

Form Field Always Null Even When Showing Data

The code below always displays opens form even when field being tested is null. Any ideas on how to fix that?



Private Sub Assign_Classes_Click()
On Error GoTo Err_Assign_Classes_Click

Dim stDocName As String
Dim msgboxstring As String
Dim stLinkCriteria As String
Me.name_found.Requery


If Not IsNull(Me.name_found) Then

DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_Load_Client_Classes"
DoCmd.SetWarnings True

stDocName = "Frm_Class_Selection"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
msgboxstring = "Client Not Found"
MsgBox (msgboxstring)
End If


Exit_Assign_Classes_Click:
Exit Sub

Err_Assign_Classes_Click:
MsgBox Err.Description
Resume Exit_Assign_Classes_Click

End Sub

Form Field Always Null Even When Showing Data

Relationships Help

Hello,

I am currently working on a database project in which I am having some trouble on! When creating relationships, I get the following error message:

"Microsoft Access cannot create this relationship and enforce referential integrity" when attempting to create a relationship between Symptoms/Diagnosis.Diagnosis AND Diagnosis.Diagnosis.'

When attempting to change Diagnosis as the FK in Symptoms/Diagnosis, I get the following message:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

The database has been attached. Thanks!

Attached Files

MIS Project.accdb (864.0 KB)

Relationships Help

Microsoft licence types

Hi all,

I volunteer for a charity and have just discovered that I might be able to get Office 2013 at a reduced price because of this. I use Office 2003 in my volunteer work at present.

However I cannot find out definitively what the differences in licence types are.?

I have the option of MOLP A, MOLP B, or Select.

Would anyone be able to advise the differences please.?

TIA

Microsoft licence types

Querry

Hello, I have a querry with this calculated field :

Expr1: DCont("[hta]","Pacientes antecedentes totales","[HTA]='1' ")/DCont("[HTA]","Pacientes antecedentes totales")*100

It is a percentage of value 1 over the total registers with a value for HTA.

If I apply a criteria in a diferent field (i.e age , gender) the results remains over all the registers with a value in field HTA , instead of a limited number of registers by other criteria .
So I can't see this results for any kind of limited registers in the same querry.
Can you help me ?
Thank you very much!

Querry

Sql

i have a table where i am maintaining the status of the record as completed or not using Yes/No.
now to generate the report i need to show (#record completed)/(#record), and there are some where clause also.

can i write like

SELECT [(Count(tbl_status = -1)/Count(tbl_status))*100] AS [Rate]" from tbl where.... group by....

Sql

Question Am i doing the Optimal Solution for a summary page?

Hello folks!,

uhm i am building an enrollment system in my school and i have created a report which gives the summary of enrollment. Specifically it will provide the summary of male,female,foreign,local,new,regular, as well as the total number of enrollment per level per department in our school. The report i am making contains 204 unbound text boxes[uniquely provides information needed] that will be populated by a vba on load. And because of this i created 188 queries for this to function, so this report will be populated on load using the codes below:

[I don't have 10 post yet: but i hope u understand the link of the code:]

http://hypertexttransferprotocolswww[DOT]dropbox[DOT]com/s/g4crdar45flkxlp/CodesForReport.txt?dl=0

Report output:

http://hypertexttransferprotocolswww[DOT]dropbox[DOT]com/s/l95c9ff0uycguym/rptEnrollmentESummary.pdf?dl=0

Now because of this, it will take me approximately 20 secs to load and finish the report. It uses approximately 40mb now in memory. I also notice a database opening slowdown (i cant determine the exact quantity, just a slowdown from before). And base from the report we have just 21 enrollees, what more if it will rise?

[When i was doing the query and code for the English Department Summary part, i t was just 3 secs.(i compared the recordset recordcount to dcount(dcount is was way slower, 11 secs, so no way that i will use d count)]

Now that the overallall loading is 20 secs, am i still doing the optimal solution with my code and queries?

Is there better solution?

Btw, i'm using MS Access 2010, the database is not yet split, plan to split yes,

sorry for the noob post, i wish some replies. tnx in advance.

[sorry for forcing to post links without minimum requirements, dont worry i will meet them soon.]

Question Am i doing the Optimal Solution for a summary page?

Show multiple records on one form.

Hey guys,

I was wondering if there was an easier way to do what I need. I need to show 10 records on one form (10 customers info), and for now I have 10 subforms to do that.

Is this the way to do it?

Thanks !

Show multiple records on one form.

Join the data of two tables using query

Hi to every one.
I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel. (Don't ask me that why you use two tables with same attributes, because i need and i can't explain my need to you).

Join the data of two tables using query

ActiveX in report

Hi all,
I have been very happily using TALtech bar code software for more than 5 years. I now need to increase the size of the human readable text (the default size is too small for the new requirement). Trouble is that when I click on the Font field in the controls Property Sheet I get the message "The operation of the TALBarCD OCX object failed. The OLE server may not be registered. To register the OLE server, reinstall it." I have tried to reinstall using Regsvr32.exe but I get the error message "The module "C\Windows\System32\Talbarcd.ocx failed to load"
I will appreciate any help please.
Regards, John

ActiveX in report

Excel VBA String (SQL) to Retrieve and Insert an Image Saved As Attachment in Access

Hi all and thanks for taking the time to even click this thread

I have a field [Screenshot] in an access table that is the data type "Attachment"

In this field I have saved a single screenshot image (.png or .jpeg)

What I want to do is insert that screenshot into an Excel spreadsheet based on a criteria ("ID") that is in Excel, sheet1 "A1" ...

Below is the VBA and therein the SQL string I use to query the Access Database; however, I can only get it to return the text (the screenshot's name in Access);

Code:

---

Sub GrabSS

loc = "C:\Database.accdb"

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & loc & ""

IDString= """" & ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value & """"
strSQL = "SELECT [Screenshot] From [Tbl1] Where [ID] IN (" & IDString & ")"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
   
cn.Open strConnection
rs.Open strSql, cn, 3, 4

 With ThisWorkbook.Worksheets("Sheet2")
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1) = rs.Fields(i).Name
        Next
        rs.MoveFirst
        .Cells(1, 1).CopyFromRecordset rs
  End With

End Sub

---

Basically, I'm wondering if this is even possible with the Attachment datatype; or whether I should be using a different datatype in Access to perform what I'm trying to do.

Thanks for any advice or help you can throw my way - :banghead:

Excel VBA String (SQL) to Retrieve and Insert an Image Saved As Attachment in Access

Sort/Filter dropdown being disabled

I'm using a dropdown that has the months of the year in it. The user selects a month and the form is then filtered by a date if its the first of whatever month is selected. It works fine but when the filter is applied I cant select the little arrow on the column headings and select the boxes (Select All, Blank, ETC...). It doesn't work on any column, just the apply text filter works but I cant select anything.

It works fine when I clear out the filters on the form. I tried to use vba to set the menusetting property to true and that didn't work either.

I hope this made sense, im kind of frustrate with it.

Sort/Filter dropdown being disabled

Passing two Cobobox value to two texbox in form

Hello All ..

fix code Please ...


Private Sub CardName_AfterUpdate()

Me.Purchasing = Me.CardName.Column(2)
Me.Selling = Me.CardName.Column(3)
End Sub

I get value of Column(2) in Purchasing textbox
but in Selling textbox not
why ?

Passing two Cobobox value to two texbox in form

DMax on mainform referencing subform

Hi I have a form - purchase, and a subform purchasedetailds

they are linked with a field "puid"

I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.

I used the control source code:
=DMax("[forms]![purchase]![purchasedetailds]![pdID]","[forms]![purchase]![purchasedetailds]","[Forms]![purchse]![purchasedetailds]![puid]=" & [Forms]![purchase]![puid])

and the simpler:
=DMax("purchasedetailds]![pdID]","[purchasedetailds]","[purchasedetailds]![puid]=" &[puid])

and i get the #error in the box can any one see what i am doing wrong?
thanks
Simon

DMax on mainform referencing subform

Using Textbox in Form to Update Subform Table

Hi! I need some help on a form I'm working on. I'll simplify this form to a
- textbox
- command button
- subform (showing a table with 4 fields, including an "EntryID" field but just one record)

What I'm trying to accomplish is to enter a value in the textbox (this value will be one of the EntryIDs in the EntryID field), click the command button, and the subform should refresh itself, showing the record from the table (the 4 fields) which has the same EntryID.

I'm still a newbie to access and I tried to google an answer but I can't seem to get this to work.
Appreciate any help on this.

Using Textbox in Form to Update Subform Table

Howdy

Hey y'all I'm Mechnech046d, from Oklahoma, USA. I'm no programmer but I have tink'ered with access, excel, and vba for the past 15 years. Somehow I always managed to Make it work. Now I have an interest in Learning more about queries VBA and bigger more complex RDMS applications. I hope i don't become bothersome as my knowledge of the DB jargon is limited to say the least. So please forgive me now for ignorance. Thanks for having me as I am looking forward to Learning and doing a lot more!

Howdy

track the neew data

hi gents,

could you helping me ,

i have an input form which insert data in table

so i need a code to track who insert the new data and in which field and what's the values that he /she enter

note:- i find acode to track changes not to track the new data

thanks

track the neew data

Beta Tester

After spending hundreds of hours designing,implementing and testing I handed over my database to a close friend of mine for some hopefully valuable input.

I am about 90% completer with it and am at a major crossroads.
I don't have users to test the data with but really wanted some expert advice on the schema,design and functionality.

It is my most intense application I have ever tried to build.
Am I adventurous and trusting or what?

:eek:

Beta Tester

Table for specific records containing hyperlink ID

Hello,

I am creating a task tracker where the user will enter a task which is working. If he is complete the task at that moment he will submit the task and everything is done for that task. However, if there is some issue or if the user need any information, then the user will enter a comment and will hit hold.

My query:confused:is once the user hits hold button, the table "Main" will capture the hold date and time. I need a second table, may be by name "Inbox" which will only show records kept on hold.

Secondly:confused: there should be hyperlink for "ID" of Hold records in table "Inbox" and when the user click on the the "ID" to resume the task, it should take the user to "Main" form and display that particular record (record which the user had clicked). Also, the date and time should be recorded when the user click on the hyperlink in Resume_Date and Resume_Time fields for that record.

Kindly request you to assist me to built my desired database.

Thank you.:)

Attached Files

Task Tracker.accdb (492.0 KB)

Table for specific records containing hyperlink ID

Customize right click menu

I have the following script which customizes the right click menu and I love it.

The problem that I am having is that I call the script to add the right click from the form open event and then multiple users can not access the same database.

I use one for forms and then another for reports.

If I manually close the open forms then reopen them then multiple users can use the same database.

If I close and reopen forms with vba the right click still remains making the database not accessible to multiple users.

It is definitely this as when I comment it out from the open event of the forms then everything is OK.

I call the following from the open event of the form as follows

Code:

---

Public Sub Form_Open(Cancel As Integer)
FormsShortcutMenu
End Sub

---

The following is stored in a module

Code:

---

Public Sub FormsShortcutMenu()

Dim cmbRightClick As Office.CommandBar
Dim cmbControl As Office.CommandBarControl

On Error Resume Next

CommandBars("cmdRightClick").Delete
Set cmbRightClick = CommandBars.Add("cmdRightClick", msoBarPopup, False, True) 'NEW COMMANDBAR
 
With cmbRightClick
   
        Set cmbControl = .Controls.Add(msoControlButton, 502, , , True) ' ViewsFormView
        'Set cmbControl = .Controls.Add(msoControlButton, 2952, , , True) ' ViewsDesignView
       
       
       
        Set cmbControl = .Controls.Add(Type:=msoControlButton) 'design view by closing and reopening form so is quicker
        cmbControl.BeginGroup = True                  ' Add a line to separate above group
        cmbControl.Caption = "Design View Quick Load"        ' Add label the user will see
        cmbControl.FaceId = 2952        ' Add label the user will see
        cmbControl.OnAction = "DesignViewFunctionFORM" ' Add the name of a function to call
       
        Set cmbControl = .Controls.Add(Type:=msoControlButton) 'design view by closing and reopening form so is quicker
        cmbControl.BeginGroup = True                  ' Add a line to separate above group
        cmbControl.Caption = "List Report Name"        ' Add label the user will see
        'cmbControl.FaceId = 2952        ' Add label the user will see
        cmbControl.OnAction = "TestingReportName" ' Add the name of a function to call
       
        Set cmbControl = .Controls.Add(Type:=msoControlButton) 'design view by closing and reopening form so is quicker
        cmbControl.BeginGroup = True                  ' Add a line to separate above group
        cmbControl.Caption = "List Form Name"        ' Add label the user will see
        'cmbControl.FaceId = 2952        ' Add label the user will see
        cmbControl.OnAction = "TestingFormName" ' Add the name of a function to call
       
       
       
        Set cmbControl = .Controls.Add(msoControlButton, 12329, , , True) ' ViewsDatasheetView
        Set cmbControl = .Controls.Add(msoControlButton, 5814, , , True) ' ViewsPivotTableView
        Set cmbControl = .Controls.Add(msoControlButton, 5815, , , True) ' ViewsPivotChartView
        Set cmbControl = .Controls.Add(msoControlButton, 21, , , True) ' Cut
        Set cmbControl = .Controls.Add(msoControlButton, 19, , , True) ' Copy
        Set cmbControl = .Controls.Add(msoControlButton, 22, , , True) ' Paste
        Set cmbControl = .Controls.Add(msoControlButton, 222, , , True) ' PropertySheet
        Set cmbControl = .Controls.Add(msoControlButton, 14782, , , True) ' close

End With

    Set cmbControl = Nothing
    Set cmbRightClick = Nothing
       
End Sub

---

Customize right click menu

Hello all

hello all nice to meet u
iam haytham from egypt

Hello all

Knock You Up

Years ago I rented an auto body shop to a distinctly different couple. He was as German as you get. His name was Hans and a long German last name. I forgot her name but she was as British as anything I had seen at least in the US. The first time I heard her say “I’ll knock you up at 6” I just about wet my draws. What brought this to my memory is that a couple of nights ago I was reading a short article and it said the saying came about was that before there were alarm clocks that there were people in London that earned their living by going around and knocking on peoples window when it was time for them to go to work. Anybody know if its truth or fiction? Sounds logical to me, but again so does Access<G>

Knock You Up

Hello

I have been using Access for several years, off and on, from Access 97 through to 2010. I only spend a small part of my time programming, so I have fairly limited skills, and always think 'There mus be a better/more efficient way to do this'

I have lurked here for a while, and found a lot of help.

Peter

Hello

Hello

I have been using Access for several years, off and on, from Access 97 through to 2010. I only spend a small part of my time programming, so I have fairly limited skills, and always think 'There mus be a better/more efficient way to do this'

I have lurked here for a while, and found a lot of help.

Peter

Hello

create table based on textbox

I have a userform that has 4 textboxes and a command button.

The user types in box1 a Rack, in box2 a shelf, in box3 a place and in box4 a shelf.

The user types in Rack textbox 18
The user types in Bay textbox 4
The user types in Shelf textbox 2
The user types in Place textbox 3

I then would like to create that amount of records in a new Temp table.

The attached file shows what the results of the output should be.

Attached Files

Location Test.xls (17.5 KB)

create table based on textbox