I have a subform that comes from a table. Depending on who is signed on I need to make some of the records (in the subform) editable, and others readonly (based on some value in the record).
For instance You have several sales managers and when a sales manager signs on to the application we know which state he is from and he can see all records (in the subform) for all states. For the records relating to states other than his state (CA) he can only access them read-only, he can't change them. For the records that have a value of State = "CA" he can change them.
Any ideas (VBA would be ok, and probably necessary).
I should be able to find the answer by searching here... But havent had any luck. Pretty simple question, just don't know the correct syntax. I have started using a form as a Menu instead of the switchboards. I have a form that one command opens in "Add" mode the other opens same form in "Readonly" mode. What I cant seem to find is how to code... "If this form is open in "Whatever" mode... THEN... Example.... Menu has DoCmd.OpenForm stDocName, , , , acFormReadOnly, acWindowNormal, stLinkCriteria Now on the form it opens I want certain command buttons to only be visible when open in certain modes... So on that forms "onload" event... (I'm guessing "onload" correct me if it should be another event)... I need to have something like
If (Form is open in Readonly) THEN cmd.whateverButton.visible = False end if
So, what command gives me the (Form is open in readonly) part?
I have a database with sensitive data. I dont want users to change the data, so i used a snapshot query. However when i use this query in another query, the result is an updateable query.
I have a subform on a form I am using to track/display events. The subform is where the user enters in the names of team members. This number can vary from 0-12 and I currently have the subform grow to display all of the team members. However, it is all in one column and I cannot seem to figure out how to change the font size in the subform (I have tried multiple methods). What I would like to do is have a smaller font size and display the team members in a manner where no more than 4 names are in a column. If 4 team members are exceeded, I would like to see this in a column right next to the first column of team members. I hope I made sense and I hope someone can give me some insight.
I have a form with multiple textboxes and comboboxes that allow for user entry, and a subform displaying a table, where the entries from the form are saved and can be retrieved. It's basically a way of creating new task entries and editing existing ones from a single interface.
One of the fields is an "issue number" of sorts, which is a 5-digit code that identifies the task. We have a website where details of each task are stored, and the URL format is akin to this: [URL] ....
The functionality that I'm hoping for is that when the user saves the record, it will be saved as the full link address, by concatenating the static first portion of the address and the code entered by the user. However, I need the table to still only display the code, not the whole link address, and will follow the full address when clicked in the subform table.
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
In my Student Administration database I am trying to launch a popup form from a sub-form of a Job Positions and Seekers form. The sub form is actually one of 2 sub-forms, both Continuous Forms default view, on a form linked via an unbound control (MasterJobPosID).
The first sub-form is called and lists open Job Positions and the 2nd sub form is called and lists all Students Seeking each of the positions. The Students Seeking subform is linked to the unbound control MasterJobPosID in the main form which is set to equal the Job Positions subform's JobPosRecNo field which is the key to the Job Positions table.
The popup form is called Job Employment and is being launched via an event macro from the Students Seeking subform. It will allow the user to create a record in the Job Employment table. The event macro has a Where statement that says "=JobPositionID And Student", an attempt to link it to both the Job Position record being filled and the Student filling it.
I am able to populate the popup form's fields in expressions setting Default Values equal to fields on the loaded Job Positions subform and the Student from the Students Seeking subform. What I am unable to get is the Job Position record key from any of the 3 fields it appears in on the Job Positions and Seekers form:
MasterJobPosID on the main form.JobPosRecNo on the Job Positions sub form.JobPositionID on the Students Seeking subform.
I want that to link the new Job employment record to the Job Position record the student is filling. In fact I get a parameter prompt for JobPositionID when the popup form is launched.
I have a form (frmMain) which contains 3 subforms and they are being set to visible at different stages of application run (depending on the options selected).
In subfrm_Main_options i have 3 buttons, clicking on 'Job Review' will make the subfrm_Search visible. In the subfrm_Search i have 2 labels, 'View Jobs' and 'View Report' and I only want the 'View Jobs' to be visible!
I have tried setting the visible property of this label to False in the subfrm_Main_Options but its giving me an error (saying the form subfrm_Search is closed or does not exist in the database!). I also tried using a Global Variable (ViewJobs - declared in module mdlPublicVars) and set it to True once the 'Job Review' is clicked in the subfrm_Main_Options form, and then look at this in subfrm_Search and then display the correct label. However after trying to put this code in various events such as, On Open, On Got Focous, On Activation, ... i didnt get the result i wanted!
Can any1 please help me out with this. I would be really grateful!
PS. The buttons appearing on the form are placed on the frmMain, I did that to see if it appears on the main form but they must be in subfrm_Search!
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) ) However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
I sent a post earlier today about making a label visible in a subform! I am still working on it but still no progress!
I have a different scenario (its the same thing but trying to explain the method i'm working on):
I have one main form (frmMain) that contains 3 subforms, say subfrm1, subfrm2 and subfrm3. On Open only 1 is visible and 2 and 3 are invisible (however they are open and loaded).
By choosing an option on 1 subfrm2 becomes available! When choosing an option on subfrm1 a boolean called 'XXX' is set to true. Now i want one particular label to become visible in subfrm2 if 'XXX' is true! To do this i need to write a code to be executed when subfrm2 is appears on screen! The problem is this form is already open so how and on which event of subfrm2 can i write the code???
At the moment i'm trying to refresh subfrm2 (by using code in subfrm1) and I have placed my code on the On Load event of subfrm2. I thought this way the form will reload and the On Loand event will pick up the new value for boolean XXX and set the label to visible! Thats the idea anyway... here is the code i have in subfrm1:
XXX = True
Forms![frmMain].subfrm2.Form.Refresh
subfrm1.Visible = False
subfrm2.Visible = True
I'm not sure if refreshing the subform will result in On Load event to be executed! And i'm not even sure that the code for refreshing subfrm2 is correct. Can any1 help me with this?
I have attached the actual database on my previous post sent today, if you prefer to look at the whole thing and get a feel of the problem!
I am working on a database used in recording device characteristics/test information. The main table of information has dozens of columns for test/part detailed information. When inputing the data for each specific test, many of the info. details are repeated when testing say 20 devices of the same part all at once. Rather than retype every piece of detailed information in every field, everytime, is there an easier way? Does anyone know of a way to make specific fields copy/paste the previous record's information in the fields automatically when a new record is created? Please, if anyone could help or has ANY ideas, let me know...
I have a field called images and its name is like abc0001, abc0002 etc, i want to update all the abc to abcd0001, abcd0002 etc, how can I do this using update query or is there any other way to do this, thanks for any help...
Hello. Im new here and also new to both Visual Basic and Access so please be patient with me :)
I've downloaded SearchDB.zip uploaded by agehoops. Its works exactly like its supposed to but I need to update it a bit.
Im making a MovieDatabase and have a table with Orginaltitle, SwedishTitle, Director, Actors and so on. When I search in "Director" for "Francis Ford Coppola" or just "Francis" the movies which is related to him comes up. But when I search for "Ford Coppola" or only "Coppola" nothing comes up. I want the user to have that possibilty to search using the last part of the name too. Is that possible?
When searching with the binocolor icon it works fine when chosing "part of record", i think thats the english translation for "Del av fält". I have a swedish acces version so im not so sure but anyhow I think you understand what I mean.
To sum it up I want the ability to search writing only a part of what the record contain including the last part and not only the first part like it is right now.
This was achieved with a form based tool available to DOWNLOAD here. (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804)
See instructions on its use here: (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
However you may be looking at the new resultant table and wondering what on earth to do with it. I hope to take you through the process of making it into something useful in this thread.
The first thing you will notice is that where the check box is not checked, then that whole row is redundant, for instance there’s no need to record that ID number “1” --- “Has Not” taken Maths, English, Geography, Physics etc, it would suffice just to record the subjects that have been taken , In this case Biology, PT and Social. Looking at those entries in particular, then a general rule of logic can be defined, “delete all the rows where the check boxes are false”.
then it becomes obvious that the check boxes themselves which now “All” contain a true value are also redundant, they can be deleted just leaving you the text entry identifying the subject taken by each student.
Using the “relational” properties of the database that is one more thing you can do which will improve efficiency and that is to replace each text entry --- Maths, English, Geography, Physics etc, with a number linking that field to a look up table.
First of all you need to create a look up table; this can be done by applying a create table query to extract just the unique values for the “subject” There is a video showing how to do this here: (At time index 1min) (http://www.viddler.com/explore/TonyHine/videos/67/fullscreen)
This unique list should be called “tblSubject” this table is not quite finished, you need to add an identity column to the left of the text representing the individual subject, this identity will then appear in the previous table.
Once you have completed the “look up table” you then need to replace the entries in the student subject table “tblStudentSubject” where it shows subject in text form with the number representing the link to the look up table. This is the query:
Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226) Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)
Video Version HERE: (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)
How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
Using the normalization form --- Download Here (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804) we converted part of a spreadsheet type table into the beginnings of a relational table.
From this table we derived a “look up table” now giving us a total of three tables, the remains of the first table, “tblStudent” (the student names), the next table “tblStudentSubject” stores the subject(s) related to each student, and finally a third table, “tblSubject” a “look-up table” to store the actual subject description.
Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:
Using the wizard create a basic form from the student table and name it “frmStudent” arrange its size so it has some open space as shown.
Now the next bit is tricky, for two reasons, the form isn’t really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.
Apologies, I have no idea if this should go in reports, queries, macros or modules and VBA as I'm a bit stuck but hopefully it's fairly simple.
I've been teaching myself access and it's been going well. What I have now been asked to do is produce a report that generates the shift patterns for everyone in the office as an HTML document. Now, the data is all exported from another program and I've had no problem getting the data into access easily. The problem I have is the format some of the data is in.
The major one that I need to solve is showing what time people are meant to take their lunch break each day. The field for break is filled in in the following format.
07/04/2008 12:45:00
Now, I have the date from elsewhere so I really don't want the date to show up so I need something that removes the date from this field. Is this going to be easy to do? (Ideally I'd like it so that the above example actually just returned 12:45 but if it has the 00 on the end that would not be the end of the world)
Apologies if this is in the wrong part of your forum.
Hi folks, I have a table with the words "Church of (whatever)" in one of the records. I am attempting to do an update query to remove the "Church of" part but leave the remaining part of the string. I am using a combination of Format and left but am not having any look. Any suggestion are much appreciated. Thanks Colin.
I'm working on this database in which I have 2 forms:
one form has personal information (PersonalInfo) and the other one is a search form (Search)
In the search form I have a field in which I want to type any part of the Name field in PersonalInfo form and I want to retrieve all the records that have this part of the name.
Here is what I tried:
In the PersonalInfo form, I want to the criteria of the NameField and typed the following:
I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.
I may be missing something here, but can any one help?
I am using a Iif statment in a query to filter records as follows:
If([value from a form]= "all holidays",("weeks hols" or "days hols"),"not a holiday")
This is returning a to complex to calculate error - I think it is a Syntax error but could be wrong but if I enter The string "week hols" or "days Hol" as the criteria without the Iif statment that works fine.
