Referencing To Users Collection For A User Table Possible?
Jul 27, 2007
My search was snowballed with lot of results about dealing with split database, seeing who's online, etc. so I'm not sure if that is a good idea.
I want to make a table of Users because my users may need to put their name on some of reports, so I could use CurrentUser() to figure out who's who and put down their proper name after comparing the username via a query.
However, I'm not sure if it's possible for me to link the user table to the user groups of database to ensure that there's no ghosts or users that doesn't have a proper name to be placed on the reports or whatever. More of a validation, really.
Is that doable or maybe there's another way to do that?
I have a master/child forms. in master form sale_id,collection notes are placed. and in child form all collection details like collectionnumber,date,etc., child form look like datasheet view.
When i select the record from the list depending on the sale_id all the collection records shown in datasheet view form. If no record is there ok. if more than one records are there in collections of sale_id.i need count of records in collection depending on sale_id and also if i select one row in collection, notes to be shown for that record when i clik on shownotes button.
If more than one record in collection automatically it increment. how many collections for the sale_id. collection number would be increment. How and also show notes depending on collection number and sale_id.
The database has to be handled by a web service and/or a web appliction making the comma delimited option occupy more server time breaking down the string into usable user_ID's. But (and this is probably my actual problem) if i was to impliment my second idea, I'm not sure what I should make the tables primary key or the best way to relate it to the main user table.
My initial thought is to just have a, technicaly useless, ID be the primary key and impliment a one(User) to many(friends entries) relationship... but im a bit of an access n00b so thought i better come ask for some advice.
I have a master table with all of my Site Names in it. I have a collection table that when a barcode is scanned it records the site name as being received. I built a query that counts the number of times the site name has been received.
What I want to do is list all of my sites from the master then display the count next to the corresponding site name. This will identify those sites that did not send in an item. Is there criteria in the query I could use so that 1) I could list all of my sites from the master table and then the count can be displayed from the collection table next to the correct Site Name?
I run a multi-user system of over 200 users. The only facility I have for advising an office-wide logout is required is the email system. However, staff either don't read their email or forget so I have to run around like a headless chicken trying to get everyone to get out/stay out of the system while I perform maintenance etc. Is there a way to enforce a logout and keep users logged out until a requested time without spending a fortune on new software? Any advice would save me a lot of hair pulling (not mine :)).
I have a database originally was created for 1 person to use. Now, this database has to be shared by various people in the company over a network. Some are only allowed to view reports while others are allowed to make changes to it. How can I convert this database to a multi-users one, and maintaining its data integrity? I can possibly restrict 1 person to use the database at any one time. I know it's not going to be easy, but with the help of you guys I am sure it can be done.
I have a db with more than 100 forms, reports - objects. My backend tables are linked sybase tables. What is the best way to hand this over to the users?
1. I want to create a mde version (which is not working, here's my problem :mad: Failure In Creating MDE File (http://www.access-programmers.co.uk/forums/showthread.php?t=94465) 2. I want a way to send this application to the user, in order that is installs on their desktop or a location i specify on the user's drive. I have no clue how to accomplish this, but i know that when i download some applications they do end up on my desktop :rolleyes: , so they must have done it somehow. Please help? 3. I want to have a master mdb hidden on the network somewhere, and when i make changes to that (e.g. my form or reports). And the next time the user goes into the version installed on their drive, it checks for the last version of the front end, and if that's not it, it erases the old one they have and installs my new version for them automatically. I know this can be done. Can you help me?
I'd really appreciate if anyone can help me please in any way. Advice, links, tutorial, whatever. Many blessings will come to you!
Ghudson! Before you attack me, i've done a search on the forum and though i've seen some posts, i can't seem to find one best for my means :) Thanks!
Currently, I have 10 Users running a front end form that connect to back end data where they add to current records and eventually check that the record is complete.
I also have an "Apply Filter" button on the right hand side of the form that allows the user to apply filters to the records to show specific data that is not complete.
I am currently hearing that while the user is working, whether they apply filter or not, it seems as though all of a sudden, all of their completed work dissapears from their recordset hence not allowing them to go back to make changes to it if needed. I can only conclude that when a different user clicks apply filter, all users are affect some how... here are some examples of the code for the filter:
Code: Select Case [cboFilterValues] ' Where the user selects a filter parameter from a dropdown Case "All Data" strSQL = "SELECT * " & _ "FROM [Data Table] " & _ "WHERE [Complete] = No
Then it does:
Code: Me.RecordSource = strSQL
I am basically trying to find a way where only the user wanting to view the filter is affected... FYI, the other users arent actually seeing all the filters from the other user filter choice, they simply loose all their completed work from their form.
I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:
Although this is pretty self-explanatory, here is a key for reference:
Code: Private Sub cmdAddMet_Click() Dim DB As DAO.Database Dim RS As DAO.Recordset Dim strSQL As String Dim i As Integer Set DB = CurrentDb
[Code] .....
This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:
Code: Set DB1 = OpenDatabase("serverotherdb.accdb") strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN 'serverotherdb.accdb'" Set RS1 = DB1.OpenRecordset(strSQL1)
However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:
Code: For i = 0 To lstFacilities.ListCount - 1 If lstFacilities.Selected(i) = True Then RS.AddNew RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i) RS!MEASUREMENT_PERIOD = Me.cboMeasure
I am trying to use a combo box to select the Company in an input record form for my Transactions. In the Transaction table, each record contains the Company ID, but not the company name (I have a relationship with a Company ID primary key in a separate table that has all the companies information).
I would like to be able to select the proper company in the combo box and have the form save the value as the Company ID number with the rest of the input data in a record (it will then refer to the correct company name in the other table if I query it because of the ID key).
Twice a year, a database of mine is accessed and put too use by various staff within a time range of 1 week. the database is on a shared drive and in a location which can be accessed by all.
The staff access the database from different workstations and in some instances at the same time.
This has only led to issues in the database being copied and then confusing staff on what database to click on thus i have 2 databases which i then have to sift through and copy/paste into the correct one.
I want to know the best way i can:
1) Prevent multiple users accessing the database at a time. 2) making a copy of the original and typing into a separate database.
have used the wizard to take values for a table dropdown field from another table
the table has
surname forename and class all of which reside in another table... ive tried to set up the bound columns so when you select a surname from the drop down the other two fileds also referencing that 6able are automatically filled in with their corresponding values.
so if the ref table had
smith john 1m
by selecting smith in the new tables drop down, the forename and class fields automatically select the corresponding values from the ref table..
im sure this is possible but cant get it to work and cant find anything on the web (although this is probably down to not knoing the right search string)
Having (with much help from many of the experts here) overcome the hurdles of junction tables, subform to subform movements, and the seemingly easy design issues of a form, I have now created a self-referencing table. I humbly return to ask for advice from those experienced with these relationships...
I have been led to this in order to load in to my DB a certain class of documents, linking them relationally to their parent documents, while performing the data input for both into the same form. What I have done and what the problem is are as follows:
I created a new field in my form (formDoc), "LinkedExhibit". In the relationship window, I reloaded the Document table and reestablished all prior relationos, then added a second (alias) table Documents1, linking the primary key of this alias to the LinkedExhibits field.
I have the Linked Exh field set up as a combobox linked to DocTitle from the Documents table ONLY for the purpose of saving keystrokes if a duplicate should arise.
Unfortunately, when I enter a new document and then continue entering data through the LinkedExhibit field, and save by moving on to another subform related to these, the Documents table shows all of what I typed, except it shows the Linked Exhibit saved as DocTitle, and the DocTitle that I had typed is gone...
I really am flying blind but believe the alias table is not linked right, or I am using that concept to do something it cannot do...
I am attaching the DB, in Access2007, if someone could lend an eye to it... I would appreciate that.
I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".
My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.
Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.
I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.
In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?
How do you do a many-to-many from one table back to that same table?
tblEvent EventID(AutoNumber) EventTitle(Memo) 1 memo content 2 data 3 more memo content 4 some info 5 more stuff 6 other text
I am trying to build a db to track all the different projects I do on my web shop www.mutzigcreations.com (http://www.mutzigcreations.com). One thing I need to do is to be able to track the different materials I make things from. This wouldn't be to bad, but sometimes I use several different materials in one project and for the life of me I can't figure out how to do a table system (or form for that matter) that would let me do this.
I build out of woods, acrylics, and other special materials so I have a table for each of the 3 main types. Then I created tables called Material-1, Material-2, etc..that did table lookups to the 3 main type tables. These tables (material-1, etc..) are then related to my main table called Creations. (is this the correct way to go about it? as I can't seem to get it to work at all)
I would eventually like to set up a form that would have a check box next to Material-1, Material-2, etc...that would enable me to select:
A. if more than one material was used B. What the materials were.
I didn't want to make several copies of the 3 main tables (wood, acyrilcs, special) as that seemed counter productive since the same info is used for each of the different material-1, material-2 selections.
Can anyone help me figure out what do to, or the best way to do it?
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database Option Explicit Public Function getCurrentRepDates() As Date Dim dtBegDate As Date dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?
(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)
I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)
But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)
The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).
It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.
For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).
I have a main menu "MainMenuF" in my database and I would like the database to be able to be searched by any of the column headings. For example I have a table titled "NewLabReportT" this table has many column headings like "Submitted By" "Reviewed By" "Title" "Abstract" etc...
I would like my users to be able to click a radio button with the same column headers on this main menu and have a box that pops up to say "Enter search parameter" and have that parameter be searched in the table "NewLabReportT" and generate a FORM not a REPORT of all of the listings in the database that have that keyword associated to that certain category searched. That way they can "CLICK" the exact field they are looking for and have it open right up to that form.
i have a raw data table built, may i know how to hide the table to not allow users to alter the raw data table?
I use the Tool > Startup option to hide everything but if the users are clever enough, they would able to make that available and try to alter the raw data table which i want to prevent and hope that's not gonna happen.
I have a table tblLineItem contains materials/line item records assign to different buyers. Each buyer/user needs to access the same table tlbLineItem and choose certain line items and works with them to create PO (purchase order) .
How do I lock the table while whoever access first then after that person finish it release the table to avoid each process of one buyer's items overlap with other buyers' line items. And if the table is being used have a message box alert to inform the buyers, so they can wait?
My problem with this database is right now I placed a check box on each line item on the form that have the data source is the tblLineItem. Each buyer filters only their line items, check the box of the line items they wish to create PO at a time and click the button "Create PO". However, if buyer A uses the same form and check the boxes on his/her own line items, while coincidently buyer B also work on the same form then when they click "Create PO", their line items cross with each other!!
I'm sorry if this isn't the correct forum to be posting in, but I wasn't sure where "I'm very familiar with Access, but I need to do X and I'm not even sure where to begin"-questions go.
I need to create a database that collects monthly metrics for an entire team. So I setup tables
I've sat and stared at this for far too long and I'm no closer. Each Metric will be assigned to a Reviewer. They will be responsible for entering the data for each Metric assigned only to them on a monthly basis. Any suggestions would be greatly appreciated.
My first post. Can you help me? I want to loop through all the fields in all the tables and find any fields with a value of "UPGRADE" The code below takes me part way there because tdf.Name gives me the database name and fld.Name gives me the field name. What I am having problems with is referencing the fields value with fld.Value. How do I dynamicaly reference the value of the field.
Any help would be greatly appreciated.
Best Regards,
Steve Wilson.
My Code: Dim StringValue As String Dim db As Database Dim tdf As TableDef Dim fld As Field Set db = DBEngine(0)(0) For Each tdf In db.TableDefs If Left(tdf.Name, 4) <> "MSys" Then Debug.Print "** " & tdf.Name For Each fld In tdf.Fields Debug.Print fld.Name & "+++ " & fld.Value If InStr(1, fld.Value, "UPGRADE") Then MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name) End If Next fld End If Next tdf
I'm fairly new to Access and was wondering if it's possible to create a database that finds MP3's in a given folder and reads the ID3 details to create records, to save entering all the song details manually?
Scenario: I have a form that allows the user to enter various parameters to build a dynamic query behind the scenes. There are 'flavors' to this query process. I have a combo box from which the user selects the query type, and then I want only the proper controls to display to support the query the user chose. I have attempted this using the following code: Code:Dim ctrl as Control Select Case mdc_cboSelectChgType.ValueCase 1 'Cust Dist - AddFor Each ctrl In Me.ControlsIf InStr(ctrl.Tag, "mdc_ca") Thenctrl.VisibleEnd IfNext ctrlCase 2 'Cust Dist - Modify Case 3 'Cust Dist - Delete Case 4 'Pending Dist - Add Case 5 'Pending Dist - Modify Case 6 'Pending Dist - DeleteEnd Select Each Case statement would be built out similar to that in Case 1. I use distinct values in the control's Tag property to determine which controls to perform the operations on.
The problem is, however, that the ctrl.Visible throws an error: Object doesn't support this property or method.
Any suggestions for how to get around this? I'm stumped...I really thought this was going to work well....
I'm probably trying to be too clever here, but I have an update process that uses many queries to update the various tables in the project.
In general, these queries must be run in the correct sequence. So I name them "update 01 chg Field1" "update 02 get Field2-3 from table2" ... "update 15 make summary table".... etc...
In VBA, I then run 'docmd.openquery ...' for each query in the list.
But as the project evolves, I have to change the query sequence, or rename the descriptive part of the name. I then have to go in to the code and change the sequence/correct the names for each.
I had this "Better Idea":
for each qrydef in db.querydefs ' only run if name starts in update if mid$(qrydef.name,1,6)="update" then docmd.openquery grydef.name ' log that the query ran, etc... end if next
This seems to work wonderfully! The qrydef are coming out in alphabetical order, which is what I want, but I can't find anywhere in the docs that says that they HAVE to come out in alphabetical order. Nothing that says they will and nothing that says they may not.
I've tested creating the queries in arbitrary sequence, or renaming the queries, and so far they are always in alphabetical sequence.
SO, is what I am doing safe? Will querydefs always return the queries in alphabetical sequence, or am I likely to have a nasty data destroying missequence at some time in the future?
