I want my primary key to be
First 3 letters of surname + first 3 letters of forename + DD + YY (Date of birth)
I can use the after update event to update the primary key field but it won't get saved for some reason so have I missed something?
My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.
how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.
I have a simple data entry form on which there are about 30 to 40 data fields to be update by users. One of the field is called "Stage" which is a combo box with 2 entries "Formal Case" and "Informal Case" with no default value whien a form is first opened.
Users will normally create a data entry which updates the table and save.
However if in future users open any one existing record and try to change Stage field from Informal to Formal or vice a versa I would then like this action to
1) update a time stamp concatinated with the stage change value in a text field perhaps
2) save/close the form
3) open a new form with all the existing entries(plus changed status of "Stage Field") with a new record ID and new time stamp.
how to Add/Update record including existence of record based on single unique field.Now I trying to learn how to check existence of record on multiple field before adding (at least two) and how to update the record with multiple field record
Below is my testing table fields
Year Month Working Days
2013 Jan 20
2013 Feb 17
2013 Mar 22
Now two situations adding a new record and updating the existing record Which means the unique fields are (Year + Month)
In my database I have a table that keeps track of a package of items. The package is assigned a package type (counter display, end cap, half pallet, full pallet for example). The record of the association of the package and it's type is held in the main table.
Each of these package types is either a case or a pallet (counter display and end cap are cases and half and full pallets are pallets) This relationship is kept in another table (we will call it description table).
Now, based on the type of package and therefore it being a case or pallet a UPC and a GTIN number are assigned. The GTIN number is different if it is a case or is a pallet. I have a table that stores all of the UPC and GTIN numbers available in 3 columns, one for UPC, one for GTIN Case and one for GTIN Pallet (the UPC is a standard 12 digit and the GTINs are 14 digit -with the first 2 different to designate pallet or case. and all are based on the check digit formula necessary)
The user assigns the UPC and correct GTIN number by clicking a button which applies the next available UPC code to the package and determines if the description of the type (case or pallet) and inserts correct GTIN number into that field. I actually have all of this functioning correctly.
Now the problem. If a user changes the package type, and therefore changes the description, I need to add code to the update event of the combo box that gives the choices for package type that does the following:
Check to see if the original package type was a case or pallet (it's description) and if by changing the package type it is now changed to the other, update the record in the main table to the correct GTIN number based on the existing UPC Code.
OR as I write this, maybe the code could simply update the main table with the correct GTIN code based on the new description and the existing UPC code. This was I would not need to check for a change just do the update every time.
I got this function which is supposed to find a record based on the primary key, and then give three fields a value if they should be empty.
Public Function DoesXrefExist(faPersonID As Long, faRoleCode As String, faDorpID As Long, faDorpCode As String)
If (DLookup("PersonID", "PEOPLE", "PersonID =" & faPersonID) > 0) Then
End Function
Thats as far as i have got...for the next part.I get the feeling that i need to use the recordset object that exists somewhere.How do i access that recordset object? I have not been using any DAO or ADO, just straight VBA. I know i can create recordsets and stuff using DAO/ADO, but there must be a recordset that has been created already by the Dlookup command...
I have an existing database with multiple tables. Two tables have the same primary key Cab#.
1. When user clicks on the form on the menu form, I want to request the Cab# value from the user.
2. Then I want to get the record from the first table CabData based on the entered Cab#.
3. Then I want to create a new record for the second table Rides with five fields from CabData table inserted into the Rides record.
4. Then I want to display the new Rides record to the user for updating.
5. Then I want to save the new record to the RIDES table.
What is the ACCESS termonolgy to do each step? Are there any special details I should watch for?
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
View 2 Replies View RelatedI have a database which is slowly evolving. Users needed a feature to delete some records without a trail and some with a trail from the form. So I added an apply action field in the subform using which they can delete a record without a trail and if they wanted to keep a trail they could do that too. When user selects "Delete Violations as it was entered in error" the system deletes the record completely which is what everyone wanted.
After six months of use now I am asked to add an audit trail. I managed to do that also. I also looked at Browne's method but my data structure does not match the requirements for that method. I used an alternate method. It works as intended except when a record is completely deleted using the code I mentioned above. Then it goes in the infinite loop. I somehow need to bypass the before update event so that the function to write the audit trail is not called.
I have attached the database ...
I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
View 2 Replies View RelatedMaking a small database, Got 1 Table.
1. ContactDetailTable
Got 3 forms.
1. ContactIDForm
2. ContactInfoForm
3. NewContactFrom
In ContactIDForm it contain 1 textbox name 'TextBox' with Button Name 'Btn'
In ContactIDForm there is only 1 Text Box ContactIDTextBox and 1 Button. User Enters ID in TextBox and On Button Click Event it should check data from TextBox in Table name (ContactDetailTable) in field ContactID and if there is record matching, ContactInfoForm should Open else NewContactForm should open with ContactIDTextBox value in it.
I have a form which displays information in the footer until the use has accomplished some tasks. Once I verify that all is well I allow the user to change the fields in the footer. I want to verify that the proper changes were made but the BeforeUpdate event does not fire until the form is closed.
View 3 Replies View RelatedI'm trying to change the name of the label in a form, upon opening the form in VBA... Which form event I should use for this?
View 2 Replies View RelatedI am new to access and I recently encountered a double click issue
My form loads perfectly on double click event but it shows the first record instead of selected record.
My search is based on a PersonID but each PersonID has different WorkID that I wish to display on double click but it always shows the first WorkID instead of my selected record
I have tried changing the filters in the form properties but it still doesn't work for me.
Here's my code:
Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "WorkForm", , , "[PersonID]=" & Me.[Searchresults], , acNormal
End Sub
[Searchresults] draws information from my Query
Query information:
PersonID... WorkID... Type......Location
1234..........1............Paint .....Address A
1234..........2............Electric...Address B
1234..........3............Floor..... Address C
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
On a form I have a pair of controls, Price and PriceInUSD. If the user enters a new value into the Price field, then its AfterUpdate event fires, which looks up the relevant exchange rate and updates the PriceInUSD control accordingly. That all works fine.
Now in some circumstances (if a checkbox elsewhere on the form is checked), I generate a modal dialog box asking the user to confirm that the Price value is correct, or enter a new value.
That modal dialogue box's Enter button's On Click event then updates the Price control before closing the dialog. That bit works fine, too - but I had thought that this update of the Price control would fire its AfterUpdate event - but it doesn't.
So, my basic question is : do Control events only fire in response to user input, and not to programmed changes ?
I have an after update event that populates, Date, Time and User into corresponding fields when the record status changes to closed.
However... Whilst the information is recorded on the form it does not record to the underlying table is there any reason for this, or anything that I am not doing correctly?
I have a form with 7 types of weights (7 textbox rows) showing 7 weights in kgs and the others in lbs.When the user update any weight-kgs textbox, the opposite weight-lbs textbox will be updated automatically, and viceversa.Now, I'd like a fast way to code this action without writing the After Update_event for every textbox (they're 14).I have already setup textbox tags differently with "WeightKGS" and "WeightLBS", thinking about using "for each ... next" statement, but I have some problems to resolve what I want.
View 4 Replies View RelatedI am working with 2 forms and a subform.
frmTaskTracker -subfrmInbox (Datasheet View - based on a query)
frmUpdateInboxItem
subfrmInbox displays a summary of tasks on a task list. The user navigates to frmUpdateInboxItem from frmTaskTracker. After updating a record from frmUpdateInboxItem, it is possible that it the record in question will no longer meet the requirements to have it listed on subfrmInbox.
I have attempted to add code to the on close event of frmUpdateInboxItem to requery the sub form on frmTaskTracker but am not getting the syntax correct.
correct my code? Alternatively is there is a more correct way to do this, I'd be happy to learn it.
Code:
Option Compare Database
Private Sub cmdClose_Click()
Me.[frmTasktracker]![subfrmInbox].Requery
DoCmd.Close acForm, "frmUpdateInboxItem"
End Sub
I have some code on a button that opens a form and changes the caption of a label:
Code:
DoCmd.OpenForm "frmRepair", , , , , , "CancelNo"
Forms![frmRepair].Form.[lblmain].Caption = "Missing Parts"
This code works well and frmRepair opens with the updated label caption. The original value was "Return/repair Information"
A few other things need to change on frmRepair depending on this caption as well as the values of some other fields, so I use the following code in the onload event (although I later tried the onopen even)
Code:
'Disable labels button if there is no RMA and the item is a repair
MsgBox Me.lblmain.Caption
If Me.lblmain.Caption = "Return/Repair Information" Then
Me.txtRMA.SetFocus
MsgBox Me.txtRMA.Text
[Code] ....
However, I cannot get this to work as the "if" statement always returns "Return/Repair information" and not the modified caption. The message box confirms that this is the case.
I suspect that this has to do with the point in time that the frmRepair loads or opens and when my code enters the modified values.
I want to recall a record by it's primary key in a form with a command button
I mean write a primary key in a textbox and press the button then it shows the record.
I have a form that has a FIND button. I want the user to enter the 3 fields that make up the primary key, then find the associated record. How can I do this in Access - not VBA?
View 1 Replies View RelatedI'm trying to create a security measure that would prevent a user from accidentally paging down in a form and moving to the next record. I want to create an event macro that would fire if the user accidentally paged down into the next record. The macro would simply move back to the previous record.
What event would I use to build the macro ? What action would return to previous record ?
I have created a form to select multiple records for a transmittal report. I added a button to the form which I would like to open the report to view the current record. In the On Click event I added the following code:
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[TransmittalID] = """ & Me.[TBLTransmittal.TransmittalID] & """"
DoCmd.OpenReport "RPTDwgTransmittal", acViewPreview, , strWhere
End If
End Sub
The debugger keeps highlighting the row above shown in red. I've used this same code before on other forms and it worked but not sure why it isn't working this time. The only difference with this form and previous ones is that this form contains a multiple values combo checkbox (hope that's the correct term). The query that the report is based on changed the names of the field names so that TransmittalID on the form is TBLTransmittal.TransmittalID in the query.
I've tried the code with and without the extended name but each time the "Do.Cmd..." is highlighted.
I would like to select a record on an already open form called "form_candidates" using a search facility but can't get it to work.
In "form_candidates" the data settings are set to "yes" (filters/edits/deletions/additions) and data entry is set to "no". On opening, the form is set to go to a new record.
In this form I have a button that opens a record search form so both forms are now open. I enter the first few letters of the surname into a text box, press a button and the listbox is populated.
In the doubleclick event of the list box I have the following code:
Dim CandID As Integer
CandID = Me.List4.Column(0)
DoCmd.OpenForm "form_candidates", acNormal, , "forms![form_candidates]![Cand_ID]=" & CandID
So I double click on the record, the candidate form is being filtered but the record isn't being shown.
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.