I am just starting find my way around Microsoft Access However I am currently stuck on something to do with table events (data macros).I added a Before Change and After Update macro to a table and added a RaiseError method to each of them.
I tried to update a record in the table. A dialog appears corresponding to the Before Change method. I deleted the Before Change method and then once again tried to update the table. No dialog box appears The After Update macro does not seem to fire.I tried all the other "After Events" and nothing seems to fire.
I am trying to update values in an unbound text box based on a selection in the combo box on the same form; however whilst I am easily able to display the information for the first selection the values do not update when I change the value in the drop down.
Hi.. I'm terribly frustrated at this point and I hope someone can help shed some light on this.. I'm running into a very peculiar situation with using "OnChange" and other events (i.e. BeforeUpdate/AfterUpdate) for one of my forms. Not sure whether anyone has ran into this problem, but I had set "OnChange" event to occur for a combo box that I have. When tested, "OnChange" NEVER FIRES (when I actually selected a different value from the drop-down box). I thought I was using the wrong event, so I tried others (i.e. BeforeUpdate, AfterUpdate, LostFocus), to no avail.
Would anyone have an idea what may be wrong? Otherwise, I will need to create a command button for my users to click there so my actions can be done..
I'm working on creating an events log. In my table I've got a field that pulls the individual's username (that they use to log into the computer) and logs it against their log entry.
As part of my query (for my daily events log report) I'm pulling up said username.
I'd like to give access to the specific user to edit his (and only his) entries from the report, but I'm getting a type mismatch with the below code:
Code: If me.UsernameID = "KraussLJ" Or Environ$("Username") Then DoCmd.OpenForm "frmShiftLog" DoCmd.FindRecord Me.ID, acStart, , acSearchAll, , acAll Else MsgBox ("You are not authorized to edit this entry") End If
(before you ask, "KraussLJ" is our ops manager who insists on having final editorial control over the shift log before sending it higher up the chain)
I can get the msgbox to run if I block out the
Code : Environ$("Username")
, but beyond that I'm a little stuck. Should this be a dlookup function or am I overanalyzing this way too much?
I have to make changes to a report that is built from a linked table that the team is using constantly. I don't know this application well at all and its going to take some analysis. How can I work with the table w/o locking the users out of the table.
I have been given a paradox .DB file that I can (seemingly) "import" to Access 2002 SP3 using File/Get External Data/Import. (Am not choosing "Link Tables"). It gets imported as Type Table: Linked Paradox. When I then open the table for a datasheet view, the following happens: (1) datasheet window appears with field headings and normal-appearing data in all records and fields (2) error msg appears "Reserved error (-5016), there is no message for this error" (3) When I close the msg box, all field data gets replaced by #Name? I can open the table in design view, but no properties can be edited.
Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog
Dim tblLink As ADOX.Table Set tblLink = New ADOX.Table
' Open the catalog. cat.ActiveConnection = cnn
With tblLink ' Name the new Table and set its ParentCatalog property to the ' open Catalog to allow access to the Properties collection. .Name = "PROJMSTR" Set .ParentCatalog = cat ' Set the properties to create the link. .Properties("Jet OLEDB:Create Link") = True .Properties("Jet OLEDB:Link Datasource") = strProvider & "F:PROJMASTER.mdb" .Properties("Jet OLEDB:Remote Table Name") = "PROJMASTER" End With
' Append the table to the Tables collection. cat.Tables.Append tblLink
Set cat = Nothing
End Sub
what am i missing??
oh, and i shouldn't see the table in the db container either, correct?
I have an afterupdate event that is supposed to send an email when a box is updated. Most of the times the code works fine except for one case.
The code checks the value of a field in subform in another tab of the same form
Code: 'find the MSM date [Forms]![Project Main]![Marketing Specialist Main]![MSMDate].SetFocus MSMDate = Nz([Forms]![Project Main]![Marketing Specialist Main]![MSMDate].value, "__-___-__")
This field (MSMDate) keeps its value in a separate table.
The problem comes when there is no value in this other table for the given project. If this is the case, the subform does not even appear on the main form (it is all a grey box) and the Setfocus returns an error.
I tried to comment the setfocus line to see what the NZ function would do in the next line and it also returns an error.
It is almost as if the subform was not "initialized" for that particular record...
I have my main form which is connected to my main table - Query 1
I have a lookup table (actually a view from SQL Server) which holds my user name - the username used to login to the database- just one value.
In my main form I have a 'Logged By' field. I want this field to default to my login name when I process a record.
I thought it would be as easy as connecting the lookup table to my main form and then in default value typing My_login.user_name.
Apparently not. It just has the #Name? in there.
Anyone have an opinion on this ?
I can use the =environ("username") in the default value which works to good effect but different users use each others PC and I would like the field to be defaulted to thier database login and not thier windows on.
Please help ! It seems so small and its driving me ceeerazy !
Here is what I want to do: I'm organising 15 training events, for 60 participants. These 15 training events fall in three themes: running, swimming, cycling. So there are 5 training events held on running, 5 on swimming, 5 on cycling. Each has on average 4 participants, but in reality, some have 6, some 2.
Everybody has to attend each of the three themes, so three of the 15 events.
I have a table called "events". each record contains data on a certain event (place, date, speaker, participants,...)
I have a table of 60 participants, with: their names, their age
Per event, I want to indicate who is participating, with a combobox. Now, the number of participants per event is not limited, so it could be 5 or 30.
Also, it could be nice if the combobox content is limited to the people that haven't been selected yet. So if John Doe is already taking the swimming training in event Swimming1, if I pull down the list of participants in event Swimming2, I do not want to see John Doe's name there.
Also, I want to calculate the average age of all the participants of a certain event.
I want to print out two reports: 1) per training event, an overview 2) per participant, an overview of all the events he has inscribed to
How do I go about this? Currently I am using 30 comboboxes, and 30 fields in the table "events", called participant1, participant2, participant3,..., participant30 But it is becoming messy, and when I ask for a report per participant, he only gives me the details for the people in participant1.
Right this is a fundamental issue for me here and I cant get my head around the problem!!!
The database I am working on is a database which records the training courses (run by the NHS) which staff at GP surgeries in our area have attended and acts as a booking system for them.
So we have the obvious tables - one for courses (including the name, cost, etc) and then one for course dates (linked by courseID) and one called training_record which contains who went to what when.
It seems simple but it has come to my attention that for example, "Course A" may in fact take place over 2 days, which could be a week apart. The surgeries are billed in one invoice, i.e. Course A would be £100 for 2 days.
How would you recommend I have this data? I can see all sorts of confusion arising... :(
The only way I have thus far thought would be to put each one in like:
course name: cost date Course A: day 1 £100 - 29/10/2005 Course A: day 2 n/a - 6/10/2005 Course A: day 1 £100 - 01/11/2005 Course A: day 2 n/a - 08/11/2005
However this seems long winded as some courses may be a few days long and this means adding each person to multiple ones!!!!
Any assistance with getting my head around this would be ace! :)
Access front end, SQL Server backend. ---- I have a continuous subform (part of a tab control on a main form) whose name and sourceobject are both "sbfCannulae". There are about 10 fields on the subform, and all are bound to fields in a table. The primary key of this table must be generated by combining two other fields, which must happen just before the record is saved. (I know calculated data should not usually be saved in tables, but trust me. In this case it is necessary.)
Right now the user COULD enter data into the primary key-bound textbox, but since the user is me, I make sure not to do that.
When I enter data into all fields except the primary key, I get an error because the primary key cannot contains nulls...of course.
I use the following code to tell me when an event has been triggered.
Code associated with sbfCannulae: Option Compare Database
Private Sub Form_Activate() MsgBox "cann activate" End Sub
Private Sub Form_BeforeInsert(Cancel As Integer) MsgBox "cann before insert" End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer) MsgBox "cann before update" End Sub
Private Sub Form_Current() MsgBox "cann current" End Sub
Private Sub Form_Dirty(Cancel As Integer) MsgBox "cann dirty"
End Sub
When I enter data into a new record, the Dirty event is triggered, but I cannot for the life of me figure out how to trigger any of the other events. No combination of values and nulls in any fields, clicking in other tabs, clicking to other records, or using the navigation buttons will ever trigger any events but Dirty.
The only msgbox I ever get is the one giving delivering the SQL error about the primary keys not taking null values.
I request your comments please... I have a form with bound and unbound fields and have linked the [Event Procedure] on these fields base on there respective action.
Now when I open or click a particular field with the event procedure, the procedure is names are like - Private Sub Rate_Exit(Cancel As Integer) or Private Sub Quantity_Exit(Cancel As Integer)
but others events are name like Private Sub Quantity_Enter() simple. Why is that ms access (Cancel As Integer) uses only for specific events only.
I have a main form that calls several other forms for the user to input data. When the user closes one of the sub forms I would like to know what event is fired when the main form gets the focus back. I have tried several different events and can not figure out which one will work. Hopefully someone knows the answer I'm going crazy tring to figure it out, oh wait I'm already there.
I have a database in which people add little bits to different fields as they go. For instance, person 1 puts in appointment, person 2 verifies info for the customer, person 3 checks the customer in, person 4 takes their money. What I am trying to do in a form is track the time when each department accessed it, and which user. I can do this on a new record, but for some reason, I can't get the event properties to work correctly on my form. I have a field for each department set aside for when they update their part of the record. What I am trying to do is, when the record is updated, having the time autopopulate the field.
Example:
Appointments enters |DATE OF APPT| |TIME OF APPT| Appointment timestamp should = time()
Reception enters |PULL TAB#| |CHECKED IN y/n| Reception timestamp should = time()
Cashier enters |AMOUNT COLLECTED| Cashier timestamp should = time()
All of these fields are on the same database (I have 2 linked tables: Customer demographics and appointments linked one to many by account # for the customer), and obviously, there's a lot more to it, but this is just a short example of what each department will enter. Appointments starts the appointment, so their timestamp will work, as they are starting new record. However, all of the following departments will be adding to this appointment record. When the other departments open their form with the fields in which they will be filling out, I want it to timestamp when they update the record as well as inserting their login "currentuser()" into the "updated by?" field for each department. For some reason, when I put time() into the event portion the field, it doesn't work. I tried it on dirty, on exit, after update, and it just doesn't update. Am I missing something?
I've set up a query to calculate the difference between 2 time fields. I set the property to short time.
The thing is - when I run the query in a Select query, the times in the column which contains the difference, appear correctly : ie hh:mm, but when I run the same query in as a make table query, the time difference field (although I have set the property to short time) appear something like this:
0.664464564646
Can somebody please tell me how to make it that the time difference results field in my make table query appears as hh:mm??
I have an issue with a code that I have in a form which adds rows in a table as many times as categories chosen from a list. However, the code is not working correctly: it adds the information and creates a row with a category in blank, in addition to the rest of the rows with one of the chosen categories. I would like for this not to happen, to add only as many rows as the categories chosen.
My code is the following:
Private Sub cmdUpdate_Click() Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset Set MyDB = CurrentDb() Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset) MyRS.MoveFirst
I have a form with sub-forms. All the subforms work smoothly, I am able to assign students to classes, enter their late/absence data etc... But there's 1 subform that I want to include the Gender (male,female) so I have to go back to the query (see attached picture) to include the table with gender data and map it with other tables, then insert a combo box in that subform, which display the gender of the student along side with other data.
But the problem is as soon as I modify the query for that subform, all the subforms (including that one) stop working, I'm not able to enter and change anything with error like Recordset is Noteditable ( see attached screenshot).
And after I remove that table from the query and the combo box from the subform every works just fine again.
I am new to Access (using Access 2012) and All I am trying to achieve is to autofill the field [Frequency] from Table FullList based on the value of [Frequency] from Table Courses using a DLookup code used to update the FullList form. The code is not updating anything! It is frustrating! Frequency is a number (integer) and while Course is a text. The figures of my Access database are below.
i have a calendar that works like a charm, but i would like to add the possibility to add recurrent events in it. for instance if a user has to perform a task at a choosen time schedule, it is shown in the agenda, like each week or each two weeks or each month. Anybody any idea how i would aproach this problem or has a sollution?
I try to implement a function that looks for broken references in my Access project, so that I won't need to modify the needed references for every workstation. Here's my problem: Where could I put the call of this function so that this code will run before trying to compile the forms and, as a result, avoiding the compile error that access pops-up when a reference is broken or missing.
I have a series of events that have a start date and an end date. How do I write an append query to append an event to a table as an individual event for every day in the given range?
I have a start and end date fields plus a txt box called wDays. I'm trying to make sure the user puts in both dates but end cant be earlier than start and vs versa.... If not provide a message as to which is missing.... If alls well do the calulation with the module "GetWorkDays" I f everything goes bad, dont save the record at all!
I have this behind a cmdButton. Used to unhide the wDays field because it displays #error otherwise...and to do the calculation... with the BeforeUpdate Private Sub Command8_Click() If IsNull(Me.sDate) Then MsgBox "Please enter a Start Date" ElseIf IsNull(Me.eDate) Then MsgBox "Please enter a End Date" Else If Me.eDate < Me.sDate Then MsgBox "End Date cannot be earlier than the Start Date" Exit Sub End If End If Me.Requery End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer) Dim myDays As Integer If IsNull(Me.eDate) Or IsNull(Me.sDate) Then Me.wDays.Visible = False Else Me.wDays.Visible = True myDays = GetWorkDays([sDate], [eDate]) Me.wDays.Value = myDays End If End Sub
Any help would be really appreaciated because I have tried many combonations of code placement to get it working correctly....
I have a split, shared database in which the Before and AfterUpdate events on my forms are not working at all. I tried setting a breakpoint to verify this and, sure enough, they are not firing.
Does anyone have any clue as to why this may be occurring?
Thanks so much for the help. I have tried searching the forum with no luck.
