I have a Form/subform that is used to allow editing of a user selected record from a table called personnel. When the form/subform is first opened, all text boxes are blank. The user selects the desired record from a combo box that uses a dropdown list to display all records of the table. When a record is selected, the contents of that record are displayed in the subform in which the user can then edit/modify. The user then clicks on a "save button" which then saves the record.
I want to use a msgbox that will notify the user that he has just updated a record and ask if he/she would like to update another record. If the answer is NO, then close form/subform. If answer is YES, I would like to clear the combo box and the subform text boxes. then the user can them start the process over by selecting the desired record from the combo box.
When I placed the msgbox coding into the "save" button's on click property, the NO portion worked fine. However, when YES is selected, the combo box would clear but the subform texboxes still show the previous record data (does not clear).
Can anyone provide some assistance?
Here is the code for the main form:
Sub SetFilter()
Dim LSQL As String
LSQL = "select * from personnel"
LSQL = LSQL & " where last = '" & cboSelected & "'"
Form_Editpersonnel_sub.RecordSource = LSQL
End Sub
Private Sub cboSelected_AfterUpdate()
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Private Sub Form_Open(Cancel As Integer)
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Here is the code for the subform "save" button:
Private Sub SAVE_Click()
On Error GoTo Err_SAVE_Click
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
If MsgBox("You have updated information on a Detachment Member. Do you wish to update another member?", vbExclamation + vbYesNo + vbDefaultButton2, "WARNING") = vbNo Then
DoCmd.OpenForm "PERSONNEL MANAGEMENT"
Else
DoCmd.close
DoCmd.OpenForm "Edit personnel"
End If
How Can I make this work Right? If anyone knows how to do this please Help. If it must be done in code please provide an example. I need to get this worked out this weekend.
I have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
I have made a form with various text-boxes, radio-buttons and other controls on it. The form is not bound to a table or query: I'm just using the form as a data-gathering device for my VBA code. After the user has entered the data I want to use a button to reset the boxes and controls to the way they were when the form was openened, ie as though the user closed the form down and re-opened it again. I know I can do this by resetting the .value of each control back to its default, but there's about 30 controls to reset! Is there not something like "forms!main.reset" that just resets all the controls a form back to their default values, clears out text boxes, etc etc as though the user has closed & reopened the form?
could someone please tell how i can reset an autonumber field to start from 1.
basically i have created a table and carried out a number of test with useless data. now that i have the table set up the way i want it and i have tested the queries etc i want to delete all the current data in the table and begin filling it with correct data. however i want my primary id to start from 1 which at the moment it doesn't because i have just deleted 50 records.
I have a quick question. I have a table that has a field with a seed identity with an increment of 1. Right now I have records in the table ranging from 1965001 - 1988704. Every so often the oldest records are archived off to another database. However, I have just been informed that the records from this table can not exceed 2000000 in the seed identity field. Ideally, I would like to just go into the table design view and change the seed identity to 1000001 and leave all of the existing records as they are, knowing that it will be years before it causes an issue. I've tried this in the lab and it didn't work, the seed identity issued was always 1 higher than the highest record in the table.
My plan B is to export all of the records out of the table, change the seed identity, and append the records issuing a new identity. The problem with this is that the seed identity is referenced in other tables, which would have to updated.
The easiest solution for me would be to be able to just change the seed identity and start issuing new seed identities, leaving the existing records alone.
I am no Access expert so please forgive me if my approach is way off.
I cleared a table of records and need to restart (at one) the autonumber field. I used the instructions found on on-line help but they will not reset the field back to one. Any one have a solution. I have even deleted the field and created a new one with the same name and it started with the same value it would hav used before I deleted any records.
SELECT DatePart("yyyy",[OrderDate]) AS AYear, DatePart("m",[OrderDate]) AS Amonth, Sum(Orders.Freight) AS SumOfFreight, Val(DSum("Freight","Orders","DatePart('m',[OrderDate])<=" & [Amonth] & " And DatePart('yyyy',[OrderDate])<=" & [Ayear] & " ")) AS RunTot, Format([OrderDate],"mmm") AS FDate FROM Orders WHERE (((DatePart("yyyy",[OrderDate])) Between 1995 And 1996)) GROUP BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm") ORDER BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm");
I have a number of tables (I know someone is going to say you don't need a primary field for these tables but I want to use one) that are basically lookup tables for combo boxes. Generally I use an autonumber to identify the ID of each record. There are occasions when all the records need to be changed (i.e. delete all old records) - does anyone know how I can programmatically (or otherwise) reset the autonumber from the last used back to "1" if all records from the table are deleted.
I have five Option Groups on my form. I was running into a problem where after selecting options for one record and then moving onto the next record, the previous options would stay selected for the new record.
I "thought" I had found a solution by setting the Option Group values to Null in the OnCurrent event of the form, but I found a problem in that when going back to a previous record, it is nulling out the previously selected options. Doh!
Any ideas on how to get around this problem? I want to be able to go from one record to the next without carried over options, but I also want to be able to go back to already entered records without having them nulled out.
I have a couple of fields on my form (Resolution Type, Date Closed) that I want to display only if the Status field has Closed as its selection for each record. I initially set the Visible parameter of both the type and closed fields to False and I reset them to visible by checking for open/closed with an afterupdate event. This works great for the first record but...subsequent entries display those 2, regardless.
I know just enough regarding events to struggle my way through on some things but more advanced field, record level and form level events are beyond my experience level.
Can anybody offer any advice on how to get those two fields to toggle visible/invisible when scrolling through the records?
With that said, does anyone have a link to a post/website referencing this subject?
I have a form that I use to collect selection criteria for reports. The controls on the form are combo boxes and date fields. After I run the report, I would like all of the fields on the form to be cleared of the selected values that were used to run the previous report. I have tried the 'repaint' method but it does not work.
I am fairly new with Access and VBA and am having troubles with the following. I filter a second combo box "cboTagNumber" with the first combo box "Combo133". The problem is when I clear the first combo box, the second combo box remains filtered. Is there an easy way to clear this?
This is the code:
Private Sub Combo133_AfterUpdate() Dim strSource As String strSource = "SELECT ID,[Tag Number] " & _ "FROM [E&I Table] " & _ "WHERE System = '" & Me.Combo133 & "' ORDER BY [Tag Number]" Me.cboTagNumber.RowSource = strSource Me.cboTagNumber = vbNullString End Sub
On a form I have 2 list boxes and a subform in a cascading arrangement. When the form is opened the first list box is populated with data from a query. When an item is selected in List Box 1 then entries appear in List Box 2 from another query. When an entry in List Box 2 is selected then entries appear in the Subform from a third query. It's all working fine except for this:- if a new selection is made in List Box 1 then I want all entries in the Subform to be cleared until something is selected in List Box 2, but I can't see how to do it.
I've tried setting the Subform's recordsource to null, to " " and to "" but none of these work properly. They either give an error or leave a single entry in the subform with #Name? in every text box.
It does work if I use a List box instead of a subform but that's not what I want here.
I have a form for student attendance that is bound to a query and stores a temporary value for ClassesAttended in a StudentEnrollment table as faculty enters the attendance. They then run an append query to write the temporary records to a StudentAttendance table. Because the ClassesAttended field is bound, when the form is opened, it recalls the last number entered for that student in that class as entered by the faculty the last time attendance was updated. I tried leaving the field unbound, but the first value enter into the first record of the form is updated automatically to all subsequent records.
Is there a way to change the properities, use code, etc. to assigne a null or 0 to the ClassesAttended field when the form opens, without the first updated record to propagate through the reaming records?
I have created a form in access that contains a multiple select list box and a command button "New Record," that creates a new record. I select values in the list box for the current record. When I click on the "New Record" button, the values that I selected in the previous record are still selected. How can I reset the list box so that no values are selected when I create a new record?
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
Private Sub PrimaryDisability_Change() Select Case Me.PrimaryDisability Case "Other (Specify)" PrimaryOther.Visible = True Case Else
[Code] ....
My problem is that when I go to a new record, the fields that I want hidden in the new record unless they meet the criteria are still visible. I have looked all over online and in books to determine the code I use or whatever I need to do to make the field invisible in the new record and only to appear in each record if it meets the criteria.
I have created quite a substantial and effective database for a small gliding club with all the major data tables being linked. I need to be able to re-index tables periodically so that running totals, which are needed to calculate statement balances, works correctly - all well and good. The only problem is that the process of re-indexing requires the data in the main table to be stored temporarily and the original data deleted. When the temporary data is appended to the main table the auto-numbering just keeps clocking up.how to reset the auto-numbering in a local table.
In month-end posting - I basically delete all the records from my transaction file. And in doing so - it seems to be resetting the auto number index - which is causing me major problems.
The resetting of the index after deleting all the records is normal.
I am working in VB 2010 - using a 2003 MS Access database.
I'm having some trouble with a report I am responsible for. I'm novice to Access, so if you could please explain in detail...that would be great.
Well, I am supposed to create a report, that will pickup data from 1 table (Passport_details), and display them in a report. Within the passport Details table, there are 3 fields (Scrap, Resale and RTS). For each record, their will only be a number in one of these fields. For example:
Product status: **here is where Scrap, Resale or RTS goes" PassportID Product Scrap Resale RTS 123456 Battery 9 0 0 987654 Pots 0 7 0 456789 Knives 0 0 3
What I want is a textbox or label at the top that will state "Scrap" if there's a value other than 0 in the Scrap Field....."Resale" if there's a value other than a 0 in the Resale Field, and "RTS" if there's a value other than a 0 in the RTS field. There isnt occurences where their may be a value for two of Scrap, Resale or RTS...its either one of them.
I'm sure this is simple for the experts within this forum, but I could sure use the help.
I need a text box in a form to display a series of error messages depending on the result of a DateDiff result, but I'm having trouble creating a single formula that encapsulates all error messages, but I have been able to get this working easily enough using 3 text boxes (For 3 seperate error messages one for results between 7 and 13 days, another for between 14 and 27 days and a third for 28+ days) and the same basic formula, I just need help combining them into a single formula for a single text box. I'm using these:
=IIf([DateDiff]>=7<14 and [Bleh] is Null, "7 Day Warning", "") =IIf([DateDiff]>=14<27 and [Bleh] is Null, "14 Day Warning", "") =IIf([DateDiff]>=28 and [Bleh] is Null, "28 Day Warning", "")
How would one go about combining all those formula's? I tried for ages today but I didn't come up with much, experimenting with Or IIf etc... but coming up with -1's all the time. Any help would be appreciated
OK, this is my first post and unfortunatly it is for some help...
I have searched Google to my wits end without any luck.
Heres my problem (partially) and I am hoping some kind person can help me.
Suppose I have a form [form1] and a query [query1].
On form1 I have a 2 textboxes. [txtProduct] and [txtStockCount].
Into [txtProduct] I enter the name of a product. In reality I have already a usable listbox in my form, but I am using a textbox for simplicity here!
Now I create an [EventProcedure] using 'On Lost Focus' against [txtProduct].
The event prodedure would partially read:
"SELECT query1.StockTotal, query1.Product FROM query1 WHERE = Product = txtProduct;"
In my actual form I am using a listbox with a listbox.rowsource = (SQL Query)to display the stock count, but I don't think this is the best way... (It works, i'll give it that!).
What I would ideally like to do is take the results from that aformentioned query and display them in txtStockCount.
What I am aiming for is to get the a query to dump its single result into a variable; not just for display purposes (txtStockCount), but to later validate user input when they wish to reduce stock values from the database, IE so they cannot produce negative stock counts.
I have a feeling this might involve Record Sets which I know nothing about at present.
If someone could either provide some working sample code snippet or point me in the right direction I would appreciate it.
I am confident enough to write SQL statements. I am semi confident in using VBA. (I am not a complete newbie).
If I have not been clear enough, I can provide a lot more information using actual details from the database I am trying to develop.
The database is a stock control system which records:
* products in one table * transactions in another table (goods in and goods out) * a query to 'sum' up 'goods' in and 'goods out' with an expression to create total stock counts for each item * a form into which a user can select an item from the product table which would then produce a single count of the stock running another SQL query against the one already created to pick out that single product (phew!)
and finally in the same form add to the transaction table.
