?
Solved

Hace Access Check if data already exists within table.  If so, complete form fields.

Posted on 2007-07-26
27
Medium Priority
?
1,172 Views
Last Modified: 2013-11-29
I have a table that functions as a subtable.  This subtable contains a list of chemicals in one product.  This table of chemicals have two fields - CAS and NAME.  The same CAS always has the same NAME.

I would like to have a subform that allows the user to enter the CAS of each chemical.  If the CAS has not been entered in the table before, then the user can enter the NAME.  But, if the CAS has been entered before, then I would like it to recognize it and complete the NAME field.  Then I would like this to be a new record in the subtable.

This way, the user does not have to retype the NAME (which can be long and difficult to spell) if they have already done so once.  Realize that there are other fields within this record, such as "Percent of Chemical" that is unique that one CAS record.

I hope I have explained this clearly.
0
Comment
Question by:iamlori
  • 15
  • 11
27 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 19576377
You could use the afterupdate event of CAS
egf

private sub CAS_AfterUpdate()
    Dim sName
    Dim bEnable as boolean

    bEnable = true
    if Isnull(Me.CAS) = False then
        sName = DLOOKUP("[NAME]","mytablename","CAS='" & Me.CAS & "'")
        if IsNull(sName)=true then bEnable = False
    end if
    me.NAME.enabled = bEnable
end sub
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19576380
actually better the other way round i.e. set bEnable to false then assign to true if not null.
Unless of course CAS is mandatory field
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19576390
my mistake, I forgot to add name to display

private sub CAS_AfterUpdate()
    Dim sName
    Dim bEnable as boolean

    Me.NAME = ""
    bEnable = false
    if Isnull(Me.CAS) = False then
        sName = NZ(DLOOKUP("[NAME]","mytablename","CAS='" & Me.CAS & "'"),"")
        if sName = "" then bEnable = true
        Me.NAME = sName
    end if
    me.NAME.enabled = bEnable
end sub


the reason for bEnable is if you dont want them to edit NAME if it already exists. If u want them to edit then remove this line

    me.NAME.enabled = bEnable

and other references to bEnable
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19576391
one way is to use dlookup

me.[Name]=nz(dlookup("[Name]", "tableName","[CAS]= '" & me.[CAS] & "'") ,"")
0
 

Author Comment

by:iamlori
ID: 19576433
rockiroads:
I get a compile error on:

Me.ChemName.Enabled = bEnable

It's not liking the .Enabled command.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19576820
is ChemName a textbox?
0
 

Author Comment

by:iamlori
ID: 19576842
Yes it is.  It's another text box on the form.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 19576898
if u comment it out, does it compile without errors?

I just realised something, if u disable one field, it will disable all in that subform
if the field is locked then you never need to worry about disabling it
will ChemName ever be editable?

0
 

Author Comment

by:iamlori
ID: 19577232
I tried commenting the line out.  And now I'm getting a message that the ChemName field is read only.

ChemName will need to be editable in the event that the CAS has never been entered previously.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19577316
Without this code in, does your form work and allow edits?
perhaps your subform is readonly?
0
 

Author Comment

by:iamlori
ID: 19577947
Without the code, the form works fine.  I'm able to add records as needed.
I do not see any sign of any form or controls being read only?
0
 

Author Comment

by:iamlori
ID: 19578028
rockiroads:
Thank you for your help!  It works now.  I don't know what I had wrong, but I tried the code again, took out the last line of code:
Me.ChemName.Enabled = bEnable

And voila!  The enable part is not an issue at this point.  Thanks for your help!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19578058
cool, glad u got it working :)
0
 

Author Comment

by:iamlori
ID: 19578211
Continuing this question - I think I know why the enabling is not working, as I'm having trouble running Me.ChemName.SetFocus as well.

I wonder if I told you this was on a subform, in a datasheet view - would it change your approach?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19578391
no it shouldnt do as datasheet is just another view

when u say .SetFocus isnt working, what happens?

0
 

Author Comment

by:iamlori
ID: 19578413
I'm trying to change the focus to another field in the subform - after it runs through the code provided above.
I added:

Me.PercConst.SetFocus

I get a compile error: Method or data member not found.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19578516
Where is this code being added, are you sure its in the right form? perhaps u added in mainform instead of subform

when you type in

Me then dot i.e. Me.

then intellisense menu appears - this contains list of methods and variables
is PercConst in there?
0
 

Author Comment

by:iamlori
ID: 19578559
Yes it is.  And then after I type dot i.e. Me.PercConst.

then the intellisense menu only shows Value as an option.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19578673
So Me.
shows PercConst

and Me.PerConst.
only shows .Value ?

okay, will look into this. Its late for me now, so will look at it tomorrow morning now

what is the rowsource for this subform? is it a table or a query
if a query, is it editable? non editable queries are those like where you have no unique id in a row, aggregate functions in it
0
 

Author Comment

by:iamlori
ID: 19582048
Thanks for sticking with this problem.
The record souce for the subform is a table.
It baffles me why .Value is the only option.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19582058
baffles me also. Is there any chance you could perhaps create a lite version of this (just a small sample of test data) and just the tables/forms required. Upload to ee-stuff or something, I can then have a look
0
 

Author Comment

by:iamlori
ID: 19591955
Sorry - I was having trouble accessing the site last Friday.  I hope you might still be available to help me.
I uploaded a small sample file at ee-stuff.  You can directly acess at:
https://filedb.experts-exchange.com/incoming/ee-stuff/4184-Sample_LMH.zip 

The subform in question is frmSubMSDS.  It functions within frmMSDS.
The AfterEvent function is on the CAS control.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19604315
ha, I see what you mean
Ive just downloaded your sample. Looking at it now
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19604348
ok, sorted - simple really

Question for you, lets see if you can figure it out :)

What is PercConst, yes you said it exists on the form but does it?
Your form is based on a table therefore you will also see the fields from the control source

when you set focus you have to specify the name of the control, and not the field

now does this make sense?

I hope so. Im trying to make you understand what the fault is.

Well here goes
The name of your control is % Concent
The name of your field from the control source is PerConst

can you now see what you was doing wrong?

Me.PerConst refers to the field in the controlsource
Me.% Concent refers to the actual control itself

Now since you have a space in your fieldname, you cant use % Concent
Luckily Access renames this control, and it has renamed it to Ctl__Concent
Me.Ctl__Concent.SetFocus
or Me("% Concent").SetFocus
take your pick

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19604351
phew, it took me longer to type my response than to find the problem. I gotta improve on my typing speeds :)
0
 

Author Comment

by:iamlori
ID: 19604608
Ah - well of course that makes sense!  I should have known that and been able to find that problem.  I've had a lot of experience with forms and should have remember the difference between field and control.  I do understand (good explanation on your part)  But poor naming convention on my part! :)

Thank you very much for your assistance and staying with the question!  I really do appreciate it!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 19604756
cool, and no worries about helping :)

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question