I have a form where people can enter multiple records. In a command button on that form I have code that assigns a unique identifier for the record. Unfortunately I am getting an error and I think it is b/c the code is not running for each record. Is there a way to tell the code to run once for each record?
THX!
I've got a form that takes the members from my members table and allows me to take attendance. I have it set up with toggle buttons in the footer (so we can see what class we're currently looking at) and I want to apply two filters when we click on a button. The two filters are "SS_Roll = Yes (or True)" and "SS_Class = AD1 (or whatever the class is)". I did some research and found one code for it, but now that I'm getting the missing object error and upon further research, I'm starting to think the code I found was only an excerpt. Below is the code I currently have. It highlights the first line when I hit debug.
Code:
Private Sub OptAD1_Click()
Table![MembersTable].FilterOn = True
Table![MembersTable].Filter = "[SS_Roll] = " & True And "[SS_Class] = " & AD1
End Sub
The problem I am facing is applying an IF statement to every record in the detail section of the subform.
I have the following code:
Code:
If Me.status = "CONFIRMED" Then
Me.course_ref.Enabled = False
Me.course_date.Enabled = False
Me.cmbModule1.Enabled = False
Me.cmbModule2.Enabled = False
Me.course_start_time.Enabled = False
Me.course_end_time.Enabled = False
Me.course_training_cost.Enabled = False
End If
This is in the on load event of the subform and works 'sort of'
Basically I have a record with the status of confirmed and records without this status, but the result of the if statement is being applied to all records. Is this because I need some sort of loop? and if so how would I loop through all records in the detail and apply this if statement to them all?
Could you please let me know how can i add record using DAO code.
Dim db As DAO.Database
Set db = currentdb
..........
.......
First off let me say there is some terrific info here on this site, great job! Also, let me state that I'm designing my first database so I'll likely be asking some really stupid questions.
I'm designing a database which will allow customer work orders to be entered. The main form is pretty big but it requires a lot of info. Eventually I want to add a search option that will allow the user to search for all records for a specific customer, or address.
Anyway, I had an Add Record button working but it was causing other problems so I had somebody help me out but now I have problems with what they added to the add record button. If some of the required fields are not filled out there is no action when the add record button is pushed. I was getting into an endless loop because the "Resume" command was in the code so I took it out. Can anybody help me with this please?
Here's the code on the Add Record button:
Private Sub addnew_Click()
On Error GoTo Err_addnew_Click
DoCmd.GoToRecord , , acNewRec
Exit_addnew_Click:
MsgBox "customer added", vbOKOnly, App.Title
Exit Sub
Err_addnew_Click:
MsgBox Err.Description
Exit_addnew_Click
End Sub
Why does this code not delete the current record?
DoCmd.RunSQL "DELETE * FROM [Tble-Readings]WHERE
[Tble-Readings].[ID]= " & Me![ID] & ";"
I have this code on the a Close Button and if the record is incomplete I want it to be deleted from the table. The ID field is an autonumber so it is the 1st field to get a value if a user starts entering data. I get a msg box saying 0 fileds will be deleted why? the id's do match but it does not delete??
jon
Ive added my own navigation buttons as I needed to add some code to them, but when a user gets to the first / last records & presses previous / next record, they get an error message 'cannot go to the specified record' and have to close & reopen the form.
It's prob. quite simple, but how do I get round this please?
Hi,
This should be an easy one...I would like to save a record part-way while filling out a form. I know this can be accomplished by pressing Shift+Enter, but does anyone know the vb code for saving a record while working on a form?
Thanks.
hola,
does anyone know the code to save a report with a changing file name?
here are the details.
i have a reoprt that changes weekly. i want to save a weekly file with the end result to be called "Downtime Cost Report #/##/##", where the date is pulled in from a qry called "qry_Actual_Costs_Thru" and field name [Fiscal Week].
i know it is something like:
DoCmd.Save acReport, "Downtime Cost Report"
Thanks
T
I have a form that shows employee license information. I also have a query & form based on that query to show licenses currently suspended. I would like to on my employees form to flash "license suspended" label if their name/record id is on the license suspension query. For all employees whose license isn't suspensed I would don't want anything to show. I can get the label to flash and to appear. I just need help evaluating the query and comparing it to the current record. Example I
if me.id = queryname.id then....execute label flashing, etc.
Can I write code to do this? Thank you
I have a simple table of Customer names. It's as simple as two fields involved:
CustomerID
CustomerName
How can I use VBA code or macro to add a new record to this table? I'm a newbie, so if possible, please provide a few lines of example codes. Thanks.
Hi,
I would like to be able to abort a record input using code rather than the esc key on the keyboard.
Is this possible, if so can anyone help with the correct code or method etc.
Sue
I'm going live with my DB tomorrow morning. I wanted to to Edited Records for my lock type but ran into a problem, this weekend when I took it to our office and had it on multiple computers for the first time. (I've been designing at my house, in my spare time (right), so I couldn't test for the problem I have now.) When a user is on a record and I open the same record up on another computer it locks ok and shows in the Record Selector that it's locked but if I begin to tab through the form I have at least one code that pukes. In my LastName field, in the On Exit event, I have the following code:
If Not (IsNull(Me.LastName)) Then
Me.LastName = StrConv(Me.LastName, vbProperCase)
End If
As I try to Exit this field, only when the record is locked, the code pukes. Can some one lend a hand and give some advise on how to prevent this?
Thanks ahead of time,
Shane
I'm trying to set up a looping code to go thru each record in a query. I know in excel I would set the cell value to a range and offset to get to the next value. How is this done in Access?
View 1 Replies View RelatedIs there code that will add a new record? I was told that the code that I am using does not actually add a new record to my form.
Here is my code:
Private Sub cmdNew_Click()
On Error GoTo Err_Ctl_New_RMA_Click
On Error GoTo Err_Ctl_New_RMA_Click
DoCmd.GoToRecord , , acNewRec
Exit_Ctl_New_RMA_Click:
Exit Sub
Err_Ctl_New_RMA_Click:
MsgBox Err.Description
Resume Exit_Ctl_New_RMA_Click
End Sub
DoCmd.GoToRecord , , acNewRec
Exit_Ctl_New_RMA_Click:
Exit Sub
Err_Ctl_New_RMA_Click:
MsgBox Err.Description
Resume Exit_Ctl_New_RMA_Click
End Sub
Hello,
I need some help. I have a form named MASTER based on a table also called MASTER.
A control of my form in names SSN which stores the client SSN.
On the same form I have placed a subform which has its record source to a table named 21.
What I am trying to do is:
When I enter a new record in my form MASTER, the code should:
1) Verify if in table 21 a record with that SSN already exists.
If YES, skip the code and do not add the SSN. If NO, well, run the appendquery.
2) At the same time, verify if a record with that SSN already exists in table MASTER. If NO, add the data, if YES, abort the code and exit the form.
The form EForms is a menu form I use to access the records.
This is the code. Something is not going right, I think with the end if functions. Any help? I have placed the code in the AfterUpdate event of my control SSN.
Dim mydb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String
Set mydb = CurrentDb
Set MyRs = mydb.OpenRecordset("master")
stDocName = "MASTER"
stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Not IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Appendssa21tax")
End If
End If
If Not IsNull(DLookup("[SSN]", "MASTER", "[SSN] = '" & Me!SSN & "'")) Then
MsgBox "Sorry! A record with this SSN is already in file. Retrive case from E-Forms Menu.", vbOKOnly, "Warning"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
End If
Forms!eforms.lstPreInterview.Value = Null
DoCmd.Close
DoCmd.OpenForm ("Eforms")
DoCmd.RunMacro ("CloseEforms")
DoCmd.OpenForm ("Eforms")
End If
Thanks.
I have a form based on a query and have written code to display a msgbox if a duplicate entry is inputted in the NHS_Number field in the form. I have also added code "me.undo" to clear the form so that it is not saved. I am looking for a piece of code that will display the duplicate record. Here is my code thus far:
Private Sub NHs_Number_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim x As Integer
[Code]....
I have database which holds records dating back 4 years. I am trying to create a code to list all records into report older than 3 years from current date, every time I run the code. The problem is I cant get the date part to work (highlighted in red) . I have tried various date options. The [datepickedup] is the date.
Private Sub Form_Load()
Dim intStore As Integer
DoCmd.Maximize
intStore = DCount("[ReferenceNo]", "[WasteCollectionRecord]", "[DatePickedup] and [datepickedup]< #" & Date & "#- 1095")
'If count of uncomplete jobs is zero display switchboardor
'Else display message box detailing amount of jobs
[Code] .....
I am currently in the middle of creating a database as a means of keeping records of vouchers codes. When someone uses a voucher, I will then be able to input the name of the customer with the voucher code in the form.
This is meant to do two processes:
1. Keep a record of who has used a voucher
2. Check and validate the voucher code (the codes are kept in a table).
Now, I have created 90% of the db to input the customers details etc BUT...I am struggling for the validation part. Ideally, the db would also remove the said voucher code from the table so the same voucher code can not be used more then once.
I was going to "pre-install" the voucher codes in the db and then print off the vouchers for distribution. But I am basically tying to make the system so that it cannot be abused (for obvious reasons)...
So I have a form which is showing the current record and you can scroll through these and make modifications to them from the form as opposed to using a table.
The form has the following fields populated from the main table
Customer name (Can appear more than once)
Status
Date
Servers
Positions
In addition it has several buttons for next record, previous record, first & last record, new record, delete record and update record.On this form i have a text box that i want to be able to use to search for a record using the customer name when you press the search button associated with it. I've got some code and it is mostly working but it seems to be moving the text entry cursor to the date box of the record for some reason.
What I want it to do is take the text from the search box and find it and move the form to that record.
Code:
Private Sub btn_cstmr_srch_DblClick(Cancel As Integer)
On Error GoTo HandleError
Dim strFindWhat As String
strFindWhat = Me.txt_cust_search.Value
[code]....
I have the following code:
techid = Forms!Main!txtCurrentUserID
Task = 2
note = "Work Instructions"
strSQL = "SELECT *"
strSQL = strSQL & "FROM [tblTracking] "
strSQL = strSQL & "WHERE ([EmpID]=" & techid & ");"
Set rs = CurrentDb.OpenRecordset(strSQL)
[code]....
For whatever reason, this code will move to the last record that was entered in YESTERDAY, but I want it to move to the last record that was entered in TODAY. If I change the code to only records that are dated today, it claims there are no records (EOF), when there clearly are. why the code will not move to the last record of the table, it is only moving to the last record that was entered yesterday.
I have a report with several sub reports that run and fill a table conditionally. The third sub report displays the records from this table. It works perfectly.
If I print a batch of records, it also works perfectly - but only for the first record. Previously, I had another report with several sub reports where the visibility of objects were controlled - this worked fine in a batch. Every subreport had the correct visibility settings.
In this case, I have a similar design, but instead of setting visibility on the various subreports, I am filling a table based on data in the subreport. As stated it works great for the first one in the group, or any single print.
I am trying to assign a unique two letter code to a set of record. From AA..AB..BA....all the way to ...ZZ, how do i go about doing this ?
View 14 Replies View RelatedI have a query where on an IIF result a field is displayed or an "OK"
I have an imported field of 6 chrs where I only want the left 5 so I use
Loc:=Left([Location],5) ,
I then compare the result in, MoveTo: to another fixed field [PreferLoc]
MoveTo: IIf([Loc]=[PreferLoc],"OK",[PreferLoc])
I wish to citeria out all the "OK" values so I have <>"OK" in the critria section but when I try to run the query I am presented with a selection box asking for Loc
This Query feeds a report so I set the filter to yes in the report properties and use [MoveTo]<>"OK" as the filter and this works great until I close and reopen the app where the filter has gone ?
Can anyone advise where I am going wrong with my query and or my report ?
thanks in advance
S
Hello Everyone
I have a form with a "Keyword Search" This functions great. It applies a filter when I do the search. Which is what I want.
But when I'm done, I want the filter removed. I can do this with a macro "ShowAllRecords". This works fine when I click on the command button it is applied to. The only thing is, it is no longer sorted by the "Auto" number field :( . I would like this to be done automatically when I remove the filter.
Is there any way to do this without clicking on my field, and then clicking on the "sort" button on the toolbar?
Thanks for any help.
:p Up to now, I've been able to find the answers to my questions by doing a search on this forum.
So thanks to everyone who has been answering everyone's questions
Joy
I have a multi-select list box for selecting which faculty members apply to a project. The faculty table and project table are linked in a many-to-many relationship. I have the following code which should create entries in the link table:
Private Sub Command5_Click()
Dim varItm As Variant
rs.Open "tblFacultyLink", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each varItm In lstFaculty.ItemsSelected
rs.AddNew
rs!FacultyID = Me.lstFaculty
rs!EntryID = Me.EntryID
rs.Update
Next varItm
rs.Close
Set rs = Nothing
End Sub
It successfully creates new records and enters the EntryID and LinkID (autonumber). However, FacultyID is always left blank. lstFaculty is the unbound list box which has three columns from the faculty table and is bound to FacultyID. Any ideas on why FacultyID isn't created in the link table (I get no error messages)?
Also, any ideas on how to prevent duplicate links being created every time the button is pushed? I was planning on having it first run a delete query for that EntryID in the link table so that it replaces the old links and any that have now been unselected are no longer linked. Comments on that idea?
Thanks again to everyone on the forums for your help.