VBA Code Problem To Verify Record

May 31, 2006

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.

View Replies


ADVERTISEMENT

How To Verify An Application

Oct 27, 2006

I'm looking for methods to verify if an application is working correclty.
Any suggestions?

Thank you in advance.

View 1 Replies View Related

Can Anyone Verify My Data Model

Jan 13, 2008

Hi I have designed a database for a small organization. It is a ver small database. can any one verify and tell me the mistakes in my design. It hardly takes 20 min for the people like you. This is my first step in designing. I am attaching the zip file which contains dbm file and business requirements. Complete physical data model is there.

especially in "category_tests" table i feel that i have done some thing wrong.

Thanking you all.

View 2 Replies View Related

Dlookup To Verify Input

Feb 12, 2005

Hi,

I'm using the code below to check new product# input from a form called frmProducts that is bound to a table called tblProducts.

Private Sub Prod__BeforeUpdate(Cancel As Integer)
Dim varX As Variant
varX = (DLookup([Prod#], "tblProducts", Forms!frmProducts.[Prod#]))
If Not IsNull(varX) Then
MsgBox [Prod#].Value & " already exists as a product #"
Me.Undo
Cancel = True
Else
'do nothing!
End If
End Sub


The problem is that any new product# that you input results in "product already exists".

Thank you very much, in advance, for any help.

Tony Scotti
Montreal

View 4 Replies View Related

Verify Date Validation Rule

May 12, 2006

I'm sure this is on the forum somewhere but have searched and searched... and had no luck.

I want my date feild to be set to enter a date no more than 2months in advance but a minium of a week in advance.

I have done this lol

week(1)<"">month(2)

I don;t actually have a clue, do you use "" to represent the value? also how do i do this date verify thing :P.

Thanks in advance

View 1 Replies View Related

Verify If Data Was Entered On Form

Oct 18, 2004

Hi all..

This is my first question posted on this site. I have created a survey with questions to be answered. The database is already populated with records of the people's names who were getting the survey. What I need is when the data entry clerk brings up the customer's name on the form, is that they are forced to enter the surveys answers for certain questions before they click the "Save" button. The answers are set up as radio buttons on the form (ie Less than 6 Months, 6 Months, 8 months and 1 Year).

Not sure if a Before or AfterUpdate macro is needed?

Any suggestions would be appreciated. Please email me directly if you have code.

Thank you

View 2 Replies View Related

Queries :: Verify Data After Certain Entries

Jun 16, 2014

Is there any way user can verify the data that its enter correctly, for example I want a button on user form, after every 14 entries when user hit that button it bring the sum of 14 entries.

View 5 Replies View Related

How To Verify A Linked Table Exists

May 25, 2014

I have a group of databases, with one that I keep global values in (among other things).On initial load, I link the table to other databases, but the linked tables pile up.How can I verify the linked table exists, so I don't have to link it again?

View 1 Replies View Related

Button Shows Verify Window Before Update?

May 17, 2006

I would like to have a button on my form that would update a record to a closed status, but I want to have an "Are You Sure?" window pop up first. I did a search on "verify," "verification," "sure," and several others but haven't found what I need. I looked through my Access bible, with no luck.

Any help is GREATLY appreciated. Thanks!

View 3 Replies View Related

Verify Imported Field Based On List In Table

May 15, 2006

Hi, I'm not sure if this is possible, but I've heard you guys really know your stuff.

I have a client who would like to import a list of e-mail addresses, but would like to verify the domain name used in the provided address against a list of domain names pre-populated in a table. Do I need to use Dlookup? Here are some additional details. Thanks in advance!

MAIN_IMPORT_TBL
ID
FNAME
LNAME
E-MAIL
PHONE

DOMAIN_TBL
DOMAIN_ID
DOMAIN_NAME

AP

View 8 Replies View Related

Modules & VBA :: Verify A Network Connection / Bad File Name Or Number

Mar 22, 2014

I have a picture stored on a network file share that populates each time a particular form is opened. Every so often we lose our connection (which is a different issue altogether). Right now the error I get after much grinding away, is "Bad file name or number"

Is there a quicker way to check for the connection before it spends 45 seconds trying to find the whole path?

Code:
Dim vFolderPath As String, dirFile As String, strFile As String
vFolderPath = Nz(DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'"))
dirFile = vFolderPath & Dir(vFolderPath & ctrl_people_id & " *", vbDirectory)
strFile = dirFile & "profile_pic.*"
'Debug.Print dirFile
On Error Resume Next
If Dir(strFile) <> vbNullString Then
Me.[ctrl_ImageFrame].Picture = dirFile & "" & Dir(strFile)
Else
Me!ctrl_ImageFrame.Picture = "X:~stuffprofile_icon.png"
End If

View 4 Replies View Related

How Can I Add Record Using DAO Code

Aug 23, 2005

Could you please let me know how can i add record using DAO code.

Dim db As DAO.Database
Set db = currentdb
..........
.......

View 3 Replies View Related

Add New Record Code

Sep 9, 2005

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

View 12 Replies View Related

Trying To Delete A Record With Code

Dec 11, 2005

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

View 5 Replies View Related

Previous / Next Record Code

Jun 23, 2005

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?

View 4 Replies View Related

Vb Code For Saving A Record

Feb 22, 2006

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.

View 1 Replies View Related

Apply Code For Each Record

Aug 19, 2004

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!

View 1 Replies View Related

Vba Code For Saving A Record

Apr 13, 2006

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

View 4 Replies View Related

Use Code To Determine If A Record Is On A Query Or Not

Jun 6, 2007

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

View 4 Replies View Related

How To Add A New Record In A Table By Code Or Macro?

Nov 8, 2007

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.

View 6 Replies View Related

Escape From A Record Entry Using Code

Feb 2, 2005

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

View 1 Replies View Related

Trouble With Code When Record Is Locked

May 8, 2005

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

View 2 Replies View Related

Cycling Thru Record In A Query In Code

Jun 12, 2005

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 Related

Code For Adding A New Record On A Form

Aug 23, 2004

Is 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

View 2 Replies View Related

Modules & VBA :: Code To Take To A Duplicate Record

Aug 13, 2013

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]....

View 4 Replies View Related

Code To List 3 Years Record?

Mar 1, 2014

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] .....

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved