I'm sure the coding is very simple, the question is .... can I build an expression using an IIF statement that checks whether the current field is the same as the same field in the next record? If it is put a 1, if not put a 0. Easy in Excel, not sure about Access??
View RepliesI have an application that enters candidates in, who apply for a job.
I found that I can enter a candidate in, and if I dont enter at least an address type or phone type ( giving that candidate an address record, albiet a blank one (just a type of address) and a phone record) then the candidate wont have a sub record for addres or phone at all.
This is hurting my reports. They are set to pull the primary address and phone only, as we dont need to have 2-3 contact information for a candidate on a report This is how I found out that I had blank address and phone for some test records. I need to be able to check when a person does a number of things, that at least the combos for phone type and address type have been chosen as something. This will ensure that they have a primary address and phone, as I have code that takes care of all that. I tried putting the following code in the form's before update, but since it is calling to a subform, the minute I tab TO the address combo, I get the message. The form is updating before I get the chance to skip the field.
Here is the lay out. A user can enter in candidate, their address, phone, application and activities. Address, phone, applications and activities are all sub reports on a tab control. The user should be able to move freely within any record, and only be bothered when they try to leave, if important information is still empty. I dont want to control how they enter ('you must enter this first!' lol) just that they do enter it in the end.
What I want to do is check when the user is leaving a record.(closing form, going to another record, clicking the search button that opens another form etc) if either combo is empty (no primary contact info) then it gives the message and goes back to the combo that is empty.
Here is the code I have. If anyone can help me find the right place to put it, I would really appreciate it. Thank you,
If IsNull(Me.[subAddressEntry].Form![cmbAddressTypeID]) Then
msgbox "You must enter an Address"
Me.[subAddressEntry].Form![cmbAddressTypeID].SetFocus
Cancel = True
ElseIf IsNull(Me.[subPhoneEntry].Form![cmbPhoneTypeID]) Then
msgbox "You must enter a Phone"
Me.[subPhoneEntry].Form![cmbPhoneTypeID].SetFocus
Cancel = True
Else: Cancel = False
End If
If I can't figure this out, I am thinking of making a new address type and phone type. Call it "none chosen" and then make that a default selection. Then one would always be chosen...I think. I would like to avoid that though.
Thanks again.
Hi Everyone
i would like to check a record exits before a new entry is saved. However i would like to check on three criterias and i am not sure on how to write the if statement.
IF Dcount "CompanyName" & "FirstName" & "Last Name" from "tblcustomer" is the same as "frmCompanyName" & "frmFirstName" & "frmLastName" > 0
Then
Msgbox "This Customer Already Exists"
Cancel = True
frmcompanyname.setfocus
End IF
Could someone please translate it into the correct code?
Many Thanks
I am running a check for checking whether a value already exists in the database table. But the problem is that the control passes right through this check.
It does not give an error too...
If txt_id.Value = DLookup("id", "indicateur", "id=" & txt_id.Value) Then
info = MsgBox("Cette 'Id' déjà existe !", vbInformation)
whats wrong with this 'IF' loop??
I would like help with a macro that looks to see if a record in my database table exists, then if it doesn't it adds a record with my data, but if the records does exist, it stops the routine and sends a message box to prompt if you want to continue with adding a new record.
The problem that I'm having is that my macro errors out when it doesn't find a record. I just want my macro to just see if a record exists or not. Then I can write my Iff statements based on whether it finds our or not. How do I just have my macro check for an record, then if it doesn't exist tell that it doesn't exist and not stop my macro. Is there some kind of IfExist() function or something?
Thanks for any help anyone can give me.
I need to know how to check if there is a record set matching a name and insert record if there is not one, otherwise just update if a record does match. See code:
Code:<%Name = request.form("name")picture = request.form("picture") opencnset rs = Server.CreateObject("ADODB.RecordSet")'if no record matches the nameSQL1= "INSERT INTO background (Name, Picture) VALUES ('" & name & "', '" & picture & "')"'elseSQL1= "UPDATE background SET Picture = '" & picture & "' WHERE Name= '" & name & "'"cn.Execute SQL1closeCN%>
Thanks in advance for any help you all can provide!!!! -Chris Gordon
dear all
i managed to get checking for duplicate record by ONE field ie member_no
code :
If DCount("*", "runner", "member_no = " & Me.member_no) > 1 Then
MsgBox " This member is already exist!" & vbCrLf
Me.c_memberid.SetFocus
Else
DoCmd.CancelEvent
End If
how i want to get checking duplicate record by TWO fields ie member_no and run_no ?
thanks in advance
How do you make the spell checker not cycle through all your records when you run it? As it is right now, when a user tries to spell check their entry into the summary block of my form it automatically starts with the first record in the database and goes from there.
This is in Access2003 BTW.
Hi.
I have 2 forms, with a "PNID" field as the link between the 2.
In the 2nd form, the PNID field allows no duplicates. I have a button on the first form to open the 2nd form to add a record.
I need some code to put on the button on the 1st form to check whether a PNID record exists on the 2nd form before opening it.......does this make sense?
What is happening is that I click on the button, try and add a record, then it tells me one exists and I cannot add a duplicate...
I have looked at the threads, and have tried using DCount? Is this correct?
Thanks
Frank.
Hello all,
I have a form that looks at my "master table" where users put in general info about a sheet of material.
There are two combo boxes on this form, Batch# and Lot#. They will use the same Batch# and Lot# for many sheets. Every time a new batch and lot are entered, this will bring up a pop-up form where the user needs to take a measurement of that batch and lot for QA. This will go into a different table, where the batch and lot #'s are unique(many of the same batch#'s with different Lot#'s).
After the lot# is entered, I want to run a query in VB that looks for those unique identifiers, and if it doesn't find a record, my pop-up form will run.
This is probably something very easy....... It was a very long weekend and I'm tired of thinking.
Thanks
Scott
Hey all,
Tables
ProductsProductBrandSize
PurchaseDetailProductPriceQuantity
Forms
frmFoodSub
Combo BoxesiProductiBrand
TextboxsiSizeiQuantityiPrice
For a couple of days now I've been trying to devise a way to achieve what I want, but I just keep going in circles and hitting errors (thanks to forum members, I've been able to solve most of them.) So, here's what I want to be able to do:
Input a product using the iproduct combo box (which gets its list of values from the Products table); its brand, using the ibrand combo box; and its size using the isize textbox, all in the frmFoodSub form. I then want it to check to see if an exact record already exists (ie. the same product, same brand, same size.)
If it doesn't exist, I want to create it. If it does, I want to do nothing. Then I want the product - either the one I just created, or from a record that already exists matching the iProduct input - to be input into the product field of the PurchaseDetails table. I then want to use iQuantity and iPrice textboxs, already linked to the PurchaseDetails table, to input the newly added product's quantity and price.
I hope my explanation was clear enough. I'd appreciate any help with this you may be able to offer.
I am a newbie to Access. I have a check box on my form that defaults to checked, meaning the record is active, what I want to do is when the box is unchecked, not have the record appear as an active record and just be on the table. I have the check box on the table and when I uncheck it on the form it unchecks on the table but don't know how to stop it from appearing in the active records. Thanks for your help, April
View 1 Replies View RelatedHi, ive got a form which requires a user to select atleast 1 option for a check box group.
when i pass the results of the form to access all of the checkboxes in the group are update although not all were selected by the user.
what can be causing this problem. The values are being passed to the correct fields in the DB but it still updates across the board.
Thanks
hey guys
having a corruption issue with my database, it happens when a user updates a record and a second user has that record open elsewhere. then when the second user closes they save their changes over the top, corrupting the whole record.
so i need to know with a recordset if there is a way to check if a second user is currently looking at the same record?
thanks
I use the code below when a form is closed to either save or undo changes. However I only want the msg box to appear if there have been any changes to the record that the form is based on, if there aren't any changes then it will just docmd.close without the msgbox. How would I check if there have been any changes to the record? I think you have to use 'Dirty' but im not really sure how to use it.
Private Sub CLoseUR_Click()
Dim strMsg As String, strTitle As String
strMsg = "Do you want to save changes?"
strTitle = " Save Changes?"
[Code] .....
I have created a form which enbles users to enter data into the fields which will add a new record to my table. What I am trying to do now is to create some kind of validation rule that will check whether the record already exists.
I need it to work this way; there are four input fields- 3 text and 1 is a date filed. When the user enters a new record they are not allowed to create a record that conatins the same combination of values. So if one record has for example filed 1 = 1 field 2 = abc field 3 = def; then no other record can have the same combination of values.
If the user enters an already existing combination there would be an error message saying which field is incorrect. If the combination doesn't exist it would add the record and display a message that the record was successfully added.
How can I do this?
I have a form, which has a combo box which a user can select an employee - the combo has three columns (Employee_Number, Surname, Initials) although the Employee_Number column is set at a 0 width, so not visible.
The form is bound to a query which only shows records that are 'Active'
When a user selects and employee in the combo box, I want the AfterUpdate event to check and see if there is already an active record - if there is, throw up an error message and then reveal a couple of buttons giving choices what to do next; if there isn't an active record, reveal three textboxes and then populate those textboxes with the values from the three columns from the combo.
The bit that is sticking me is the search - - I have tried DCount, but can't seem to get it to work
Here is the section of code that I am battling with:
Code:
Dim EmpNo As String
EmpNo = cboEmp_Check.column(0)
If DCount("Employee_Number", "Incomplete_Training", "Employee_Number=" & EmpNo) > 0 Then
MsgBox "Existing", vbOKOnly
1) If I check recordcount without move rst to last position I get wrong number of recordcount.
Code:
rst = CurrentDb.OpenRecordset(strSQL)
msgbox nz(rst.RecordCount, 0)
2) If I move rst to last position, number of recordcount is correct, but! If there's no record in my string I get an error that rst can't move to last position.
Code:
rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveLast
msgbox nz(rst.RecordCount, 0)
how to check correctly RecordCount even if my query in string has no record?
1) If I check recordcount without move rst to last position I get wrong number of recordcount.
Code:
rst = CurrentDb.OpenRecordset(strSQL)
msgbox nz(rst.RecordCount, 0)
2) If I move rst to last position, number of recordcount is correct, but! If there's no record in my string I get an error that rst can't move to last position
Code:
rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveLast
msgbox nz(rst.RecordCount, 0)
So the question is, how to check correctly RecordCount even if my query in string has no record?
I am trying to check if a record exists in a table.
Dim cert As String
Dim existingRec As Variant
existingRec = DLookup("[Certificate]", "[Fire]", "'cert' = 'cert'")
If existingRec = Null Then
Else
MsgBox "The Certificate " & existingRec & " is in the database"
End If
Note - Certificate is a column in the table "Fire"
Problem is that existingRec only finds the first value in the Certificate column, How do I get to check the entire Certificate column?
How do I check, before opening the form, whether any records exist in the table bound to the form?
Thanks
I have a multi user database that has record locking turned on at the form level. If one user is in that form making changes it locks that record so no other user can make changes. When another user tries to edit the same record they are only notified after they hit save. Can you have them be warned that this record is locked before they waste time entering data?
View 1 Replies View RelatedWe have a table, JobRegister that people enter information on using a form, FrmOrderEntry
I would like to run a check when a serial number is entered in the field SerialNo to see if the same serial number exists where CompletionDate is null.
this would stop duplicate open orders being put on the system, or new orders being entered where the previous order hasn't been completed.
Payroll main form to post attendance record. Before I open the attendance form, I wanted to check if there is a record in the sql statement.
The code below produces error MS Acces object "Variable or With Block Variable Not Set"
Code:
Private Sub cmdPostAtt_Click()
Dim stSQL As String
Dim dbHBK As DAO.Database
[Code].....
I have a subform that populates from a query from a table. I also have a checkbox as part of the table. If the checkbox is ticked, I don't want that record to show in the subform
View 4 Replies View RelatedI currently use
Code:
strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
To insert new departments into a table, however id like it to check to see if a department name exists in tbldepartments.department to prevent duplicates being added?