Hello from the Carolina's

Hello all,

My name is Jim and I'm originally from Cleveland but now live in North Carolina. I'm in my early 30's and stay as busy as possible with my hobbies. I like to build furniture, improve my home (working on an addition which will be a man cave), I build motors/restore cars, I play a lot of basketball. I am by no means a proficient DB builder but am getting a lot better at it as the days go along.



Some of my DB projects include a DVD/music library/home automation program, employee management tool and an inventory inspection tool.



The DVD,music and home automation db essentially started off by taking all of my DVD's/CD's and ripping them to my home server. It was approximately 1300 DVD's and roughly 500 albums. It took a very long time to get knocked out but once I did I built a DB that I could look up a movie or song by artist, actor, genre. I can look up by multiple artists, actors or genre's as well. It then populates a report from the query results which has hyperlinks to the files location on my server. All my smart TV's are linked to the server and I have the ability to watch the same movie on all the TV's, one TV or multiple movies on multiple TV's.

The home automation is a work in progress and allows me to open and close my garage doors, automated door locks. I am working on figuring out how to incorporate my security camera's for remote view and also talk over my intercom system via my phone. Those are proving to be very difficult but fun to work.



My employee and inventory DB's are pretty basic. They are simple with only >=[forms]![formname]![txtdatestart] And <= etc. type date range query criteria along with Like & "*" [forms]![formname]![field] & "*" data isolation criteria. With those, my philosophy was to keep it as simple as possible so not much can go wrong.



Most of the stuff I've learned along the way came from this forum and I thought it was time to stop lurking and officially thank all of you for the great posts and information you all provide.



That about wraps it up so hopefully I may be able to contribute something to the collective and I wish you all a great day!

Hello from the Carolina's

Tabs

Good Day!



I have been asked to make a database for tracking employee data. In this database, the manager wants to be able to select what tabs (subforms) the employee can fill out.



I was thinking of using a form and creating a tab system inside the form that allows the employee to click on the form they need to fill out, but my concern is that I will have to create a separate form with different tabs for every variation of the tabs or is there a way to "hide" tabs that they don't need to see, through some form of selection process in another form possibly?



I hope that this explains what I am trying to do well enough. I am also not averse to coding something, but my coding work is still in the basic stages.



SafetyGuy

Tabs

Developing Access DB - Am I going about things the right way?

Hey Everybody!



It's been a long time since I wrote an introductory message and due to my work duties becoming focussed on some other areas I forgot all about this forum and the resources it could provide me. So sorry for my absence and forgive my impertinence in coming to you with all of this!



As I am now coming to construct what is essentially my first "proper" Access database I really wanted some guidance to make sure I am on the right lines before I get too far in the development process. Apologies in advance for what will inevitably be a long-winded post; I'd rather give as much information now than having to fill in bits as I go along.



Background



The company I work for is a non-profit association of insolvency \ bankruptcy \ turnaround management companies. One of their primary duties is to facilitate the running of various conferences and seminars throughout the world during the course of each year. There is generally one major annual conference and 4-5 smaller seminars.



Prior to being hired by the associations, all IT work was either outsourced or cobbled together by members of staff with no massive degree of technical expertise - as mentioned, we're non-profit so everything has to be developed as a cost-effective and needs-must basis.



As a result, the database that I have been asked to re-write is archaic at best, and designed without much thought for standard database design principles. (And to make matters worse when it comes to making use of any of the pre-existing data, the database was written in Access 97!)



Aim



So, having said all that. I am trying to design a database that will allow us to store information about the conferences \ seminars (hereafter referred to as event for ease), the registered delegates, the sessions at the event that they have requested to attend, any social functions they wish to attend and also to record financial information for invoice and auditing purposes.



My intention is to have a DB that will be usable from event to event, eliminating the need for multiple databases. I would certainly want to split the database to allow this so that front-end objects are separate from back-end objects but a comprehensive design of this nature might be beyond my capabilities at this point.



I have developed a number of tables and fields - attempting to normalise as best as possible - and have reached the point where I am looking to define relationships for the tables but as stated above, want to make sure I'm doing the right thing so far before I get ahead of myself. I'm confident that once the underlying data structure is established that creating the necessary forms and reports won't be too much trouble.



So far...



I have established the following tables and fields:



tblBookingInfo

BookingID - PK

DelegateID - FK

EventID - FK

BookingDate

BookedBy

BookingStatus

PaymentMethod

BadgeName

AccompanyingPerson

HotelInformation

DietaryRequirements

Remarks



Comment 1: Not sure if I am right in including 3 ID fields (AutoNumbered in the relevant tables below).



Comment 2: BookedBy has been implemented thus far as a lookup field populated by information in "tblEmployeeInfo" below. As per your commandments I fear this is already incorrect?



Comment 3: Added PaymentMethod and linked it to tblPaymentMethod in the same way as above.



tblBreakoutFuncInfo - Omitted at this time as I am not entirely sure of the best implementation or if this is necessary but this table would list the different breakout sessions (A1, A2, A3 ... B1, B2, B3 and so on for example) and Social Functions for each event so that this information can be attributed to a booking. Possibly would require two separate tables to differentiate Breakout designations from Social Functions?



tblCompanyInfo

CompanyID - PK

CompanyName

CompanyAddress1

CompanyAddress2

CompanyTownCity

CompanyStateProvince

CompanyZipPostalcode

CompanyCountry

CompanyPhone

CompanyFax

CompanyEmail

IsG36?



Comment 1: IsG36 is a Yes\No value which would be used to signify whether a company is a member of the Group of Thirty-Six.



Comment 2: Am I right in using Address1 \ Address2 fields or should this be avoided? I query this as being an international association there is no one fixed address style that we would be able to use.



tblDelegateInfo

DelegateID - PK

CompanyID - FK

DelegateTitle

DelegateFirstName

DelegateInitials

DelegateLastName

DelegateSuffix

DelegatePhone

DelegateFax

DelegateEmail

IsMember?



Comment 1: Again, "IsMember?" is a Yes\No field to signify whether the registered delegate is in-fact a member of our association because we also have delegates who are not necessarily members. This is an entirely separate matter to "IsG36?".



Comment 2: Again, concerns about the address format are as above



tblEmployeeInfo

EmployeeID - PK

EmployeeFName

EmployeeLName

EmployeeInitials



Comment 1: I initially thought to use tblEmployeeInfo as means to populate "BookedBy" in tblBookingInfo Possibly wrong, as I notice your commandments recommend the avoidance of lookup tables?



tblEventInfo

EventID

EventName

EventStartDate

EventEndDate

EventIsSeminar?

EventVenue

EventAddress1

EventAddress2

EventTownCity

EventStateProvince

EventZIPPostcode

EventCountry

EarlyMemberFee

EarlyNonMemberFee

LateMemberFee

LateNonMemberFee



tblPaymentMethod

PaymentID

PaymentMethod





Relationships



Now, as far as relationships go the following facts can be said to be true:



- An Event can have one or more Delegates

- An Event can have one or more Bookings

- A Delegate can attend one or more Events

- A Delegate can only have one Booking per Event

- A Delegate can belong to only one Company

- A Booking can be made by only one Employee



So, what I'd really like to know is, am I doing anything fundamentally wrong at this point or anything that is likely to cause me problems further down the line?



I've read that Many-to-Many relationships are not very common but based upon the above it looks like I'd end up with a few Many-to-Many relationships. tblBookingInfo looks to provide the link to-and-from a number of other tables, is this OK or is there a better way of doing this?



To anyone who has read thus far, my thanks and appreciation in advance for any assistance you might be able to provide. Also, given the magnitude of the project there will probably be things that I've missed out or haven't clarified so please let me know and I'll fill in any blanks!

Developing Access DB - Am I going about things the right way?

[SOLVED] Index or duplicate key violation

Hello Experts,

what am I not seeing? It must be so obvious that it baffles me.I am trying to add a "T" in front of an article number and write this new number to a table.

Access cannot find the new Article strArt because it does not exist. When I put a msgbox after "if .nomatch then" it correctly shows the message. Yet it refuses to create the new record because of a key/index violation. Apart from an index on the recordkey, it has an index on Artno, both indexes do not (obviously) want duplicate values.



Here is the code:

Private Sub btnCopyT_Click()

Dim db As Database

Dim rstArt As Recordset

Dim strArt As String

Set db = CurrentDb

strArt = "T" & Me.frmNomenclatuurSubfrm.Form.Artno

Set rstArt = db.OpenRecordset("tblArticle", dbOpenDynaset)

With rstArt

.FindFirst "Artno = '" & strArt & "'"

If .NoMatch Then

rstArt.AddNew

!Artno = strArt

rstArt.Update

End If

End With



End Sub

[SOLVED] Index or duplicate key violation

make pdf file from a report

Hi,



i have the following code to make the pdf form a report. but i want to make the pdf in landscape mode and also each time with a new name.

can you help me please?

how can i change it?



If Me.Combo3.Value = "" Or IsNull(Me.Combo3.Value) Then

MsgBox "Please enter order number!", vbExclamation, "Order number required"

' ElseIf Me.cmb_AuftragNummer.Value Like "*[a-z]*" Or Len(Me.cmb_AuftragNummer.Value) <> 6 Then

'MsgBox "Invalid order number!", vbExclamation, "Invalid order number"

Else

DoCmd.OpenReport "rep_CQAReport", acViewPreview, , "[Fehlercode] = '" & [Combo3] & "'"

DoCmd.Close acForm, "frm_rep_cqaReport_filter"

DoCmd.OutputTo acOutputReport, "rep_CQAReport", acFormatPDF, "O:\Applications\CQA Reporting\PDFTest01.pdf", False

End If



thank you

make pdf file from a report

Data source name not found error

Hi All,

i recieved error on the following connection string in the combo box change event

code as follows,



Private Sub LotNo_Change()

Dim conn As New ADODB.Connection

Dim sConnString As String





'Create connection string

sConnString = conn.Provider = "SQLOLEDB; Data Source=SDSACCOUNT; Initial Catalog= SSPLCO; user ID=sa; Password=sa;"



'Open connection and execute

conn.Open sConnString



'Do your query

conn.Execute "Select ITEMNO FROM ICITEM;"



End Sub

Any Solution?

Thanks in advance...

Data source name not found error

How to unified same tables for 3 different MS Access file

Hello Everyone!



I hope this is the right thread I'm posting.



Scenario



I have 3 sets of MS Access File (NEDA, RDC,& Summid) built with the same sets of structures. Originally at MS Access 2007, but now I'm using the built in 2010 version for Lenovo Flex 2.

The table consist of:

*Chart of Accounts

*Journal Entry Voucher Master File

*Journal Entry Number Master File

*Supplier Master File

Queries are the same (links, designed, etc...)

Forms are also the same (links, designed, etc...)

Reports are also the same (links, designed, etc...)



Problem



The difference for these three file is the Journal Entry Number because each file requires a different JEV format

Example:

For NEDA it should be NEDA-Year-Month-Number Series

For RDC it should be RDC-Year-Month-Number Seried

Laslty for summid it should be Summid-Year-month-Number Series

and separate transaction listing for the purpose of calling data for the report, such as List of disbursements and trial balance.



But for these three MS Access file, Chart of Accounts and Supplier Master File do have the same set of data. That's why whenever a data is not available I am encoding the said information in each MS Access file (inputting the data three times).

Example:

If an asset account such as Office Supplies is not available, I will input in the Chart of Account Form for NEDA, then RDC, then Summid.



Question



Is there a way where I can unified the Chart of Accounts and Supplier Master File into one but still can entry/edit the same in each MS Access forms. Also can still link to each queries, tables,and reports?

So that I will encode data one time only and save space.





Any comment, suggestion, violent reaction and Ideas are very much welcome.



Hoping to hear from you soon.



Thank you,

Chino :)

How to unified same tables for 3 different MS Access file