Cigar Asylum Cigar Forum  

Go Back   Cigar Asylum Cigar Forum > Non Cigar Specialty Forums > Misc > General Discussion

Reply
 
Thread Tools Display Modes
Old 08-10-2011, 10:07 AM   #1
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default Anyone here a Microsoft Access pro? [Plea for help]

For the last few weeks I've been building up a database that will be turned into a SQL server in the future (and I'm learning as I go - I haven't used access for anything more than as an excel table that multiple people can alter at the same time). This is for management of data entry, reporting, etc. All the things you'd expect a database to do

I was reviewing the first form (Creating a new user) that I created within this database and realized that I created it before we agreed to follow Leszynski's naming convention. I renamed the fields and changed the VBA coding to reflect this. After breaking everything (Hurray!) and fixing most of it, I have hit one point that I haven't been able to correct. The code is shown below:

Quote:
Private Sub tglNextRecord_Click()

'Verify Production causing entity
If [chkProduction] = Yes Then
DLookup("[Production]", "tblUserPCE", "[UserID] ='" & [TempVars]![tvCurrentUser] & "'") = Yes
Else: MsgBox "..."
End If
'Verify Handling causing entity
If [chkHandling] = Yes Then
DLookup("[Handling]", "tblUserPCE", "[UserID] ='" & [TempVars]![tvCurrentUser] & "'") = Yes
Else: MsgBox "..."
End If
The error I get is Error 424 - Object required. It flags the DLOOKUP("[Handling]",...) line. This line should not be operating as I have chkProduction with a check and chkHandling without. Since [chkHandling] = No it shouldn't even attempt to run..right? This worked prior to renaming the fields, however I have double, triple, quadruple, quintuple checked to ensure that the field name matches the VBA code.

The TempVars pulls the UserID of whomever logged into the database in the first place. It functioned in the chkProduction If statement so I do not believe that is the issue.

Any ideas? I'll be back from lunch in 45 minutes if you all wouldn't mind having this solved by then
Posted via Mobile Device
Apoco is offline   Reply With Quote
Old 08-10-2011, 10:19 AM   #2
elderboy02
Lets Go Buckeyes!
 
elderboy02's Avatar
4
 
Join Date: Oct 2008
First Name: Dan
Location: Cincinnati, OH
Posts: 12,495
Trading: (133)
Bolivar
elderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud of
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Damn. This is what I do for a living and I am baffled by this...

Let me think some more.
elderboy02 is offline   Reply With Quote
Old 08-10-2011, 10:22 AM   #3
CigarNut
F*ck Cancer!
 
CigarNut's Avatar
16
 
Join Date: Jan 2009
First Name: Michael
Location: Clermont, Florida
Posts: 18,038
Trading: (111)
RA
CigarNut has disabled reputation
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

I would put

Msgbox "[UserID] ='" & [TempVars]![tvCurrentUser] & "'"

right at the top of the routine just in case.
__________________
Need Beads? Need Five Finger Bags?

2 of 3 Requirements for use of the CA Rolodex: 100 posts/ 60 day membership/ participation in trade (trader rating). New members can be added at any time.
CigarNut is offline   Reply With Quote
Old 08-10-2011, 10:32 AM   #4
elderboy02
Lets Go Buckeyes!
 
elderboy02's Avatar
4
 
Join Date: Oct 2008
First Name: Dan
Location: Cincinnati, OH
Posts: 12,495
Trading: (133)
Bolivar
elderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud of
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

OK. I am stumped. I would do what CigarNut advised in the post above.
elderboy02 is offline   Reply With Quote
Old 08-10-2011, 10:51 AM   #5
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default

Quote:
Originally Posted by elderboy02 View Post
Damn. This is what I do for a living and I am baffled by this...

Let me think some more.
Yeah. It seems to be a doozy. And I have no training in access and I haven't done any form of "coding" (if you can call what I did coding) in YEARS. The power of google is strong with this one so far

Quote:
Originally Posted by CigarNut View Post
I would put

Msgbox "[UserID] ='" & [TempVars]![tvCurrentUser] & "'"

right at the top of the routine just in case.
It returned the stored TempVar - Needhal (my test loginID - built out with certain specs seperate of what I will actually use). It is pulling the correct value - the kink in the chain has to be that it does not understand what to do if the box is set to No. I checked the property sheet to ensure [Event Procedure] was listed in the "On Click" field and it was/is. I'll keep trying to google it and figure it out. Most of what googling has returned so far is not having [Event Procedure] in the right place (which also doesn't make sense. If access doesn't know to look for the VBA code and therefore ISN'T running it...why does it kick back an error )
Posted via Mobile Device
Apoco is offline   Reply With Quote
Old 08-10-2011, 12:31 PM   #6
CigarNut
F*ck Cancer!
 
CigarNut's Avatar
16
 
Join Date: Jan 2009
First Name: Michael
Location: Clermont, Florida
Posts: 18,038
Trading: (111)
RA
CigarNut has disabled reputation
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

This is one of the reasons I don't use access directly (anymore). Do all my coding in ADO on webpages and then I can use Acess or SQL Server databases and it all works...

Good luck!
__________________
Need Beads? Need Five Finger Bags?

2 of 3 Requirements for use of the CA Rolodex: 100 posts/ 60 day membership/ participation in trade (trader rating). New members can be added at any time.
CigarNut is offline   Reply With Quote
Old 08-11-2011, 06:38 AM   #7
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

OK. So I slept on it for the night and came in ready to try to fix this error. It stops giving me the "Object Required error if I rearrange the formula from this:

Quote:
DLookup("[Handling]", "tblUserPCE", "UserID = '" & Forms![frmNewUser]![strCurrentUser] & "'") = Yes
to this:

Quote:
Yes = DLookup("[Handling]", "tblUserPCE", "UserID = '" & Forms![frmNewUser]![strCurrentUser] & "'")
The issue is that the DLookup is still not functioning.

SO...what I have done instead is set up a separate, hidden set of checkboxes that will run as soon as the user opens the form. It will populate the checkboxes from tblUserPCE/tblUserDisposition into frmNewUser.

I copy/pasted the exact DLookups that weren't functioning before...and now they work perfectly.

This is a temporary, ugly fix. There is some issue with my syntax. I do not understand why it is not accepted Dlookup()=Yes and is instead forcing Yes=Dlookup(). I also do not understand why Yes=Dlookup() breaks the DLookup()

In summary: Access sucks.
Posted via Mobile Device
Apoco is offline   Reply With Quote
Old 08-11-2011, 06:41 AM   #8
elderboy02
Lets Go Buckeyes!
 
elderboy02's Avatar
4
 
Join Date: Oct 2008
First Name: Dan
Location: Cincinnati, OH
Posts: 12,495
Trading: (133)
Bolivar
elderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud of
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Quote:
Originally Posted by Apoco View Post
OK. So I slept on it for the night and came in ready to try to fix this error. It stops giving me the "Object Required error if I rearrange the formula from this:



to this:



The issue is that the DLookup is still not functioning.

SO...what I have done instead is set up a separate, hidden set of checkboxes that will run as soon as the user opens the form. It will populate the checkboxes from tblUserPCE/tblUserDisposition into frmNewUser.

I copy/pasted the exact DLookups that weren't functioning before...and now they work perfectly.

This is a temporary, ugly fix. There is some issue with my syntax. I do not understand why it is not accepted Dlookup()=Yes and is instead forcing Yes=Dlookup(). I also do not understand why Yes=Dlookup() breaks the DLookup()

In summary: Access sucks.
Posted via Mobile Device
That really is strange. Usually in VB/VBA you have to have your variable on the left. I manage a word template here at work with TONS of lines of code. We use it for a complex exam the physical therapists do. I too have to use hidden fields in order to get the darn thing to work right. VBA really is a b***h to work with.
elderboy02 is offline   Reply With Quote
Old 08-11-2011, 06:49 AM   #9
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default

Quote:
Originally Posted by elderboy02 View Post
That really is strange. Usually in VB/VBA you have to have your variable on the left. I manage a word template here at work with TONS of lines of code. We use it for a complex exam the physical therapists do. I too have to use hidden fields in order to get the darn thing to work right. VBA really is a b***h to work with.
Exactly! It kicks back an "Object Required" error if my variable is on the left...and it doesn't function when the variable is on the right.

I just had a thought. Maybe instead of having it check all of them at once I should have them checking every time the user updates a given field...that might work...I'll be back in a few. Time for more testing.

EDIT: OK. If I instead run a code that looks like this:

Quote:
Private Sub chkProduction_AfterUpdate()

If [chkProduction] = Yes Then
DLookup("[Production]", "tblUserPCE", "[UserID] = '" & Forms![frmNewUser]![strCurrentUser] & "'") = Yes
Else: [chkProduction] = ""
MsgBox "You may not create a user that can authorize Production issues"
End If

End Sub
And force it to check after each update it will work just fine on top of automatically changing the field to "No".

WHY DOES THIS WORK, BUT MY FIRST SOLUTION DOESN'T. *rage*
Posted via Mobile Device
Apoco is offline   Reply With Quote
Old 08-11-2011, 06:56 AM   #10
elderboy02
Lets Go Buckeyes!
 
elderboy02's Avatar
4
 
Join Date: Oct 2008
First Name: Dan
Location: Cincinnati, OH
Posts: 12,495
Trading: (133)
Bolivar
elderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud ofelderboy02 has much to be proud of
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Ha ha brother. You too have the VBA rage
elderboy02 is offline   Reply With Quote
Old 08-11-2011, 07:19 AM   #11
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default

Quote:
Originally Posted by elderboy02 View Post
Ha ha brother. You too have the VBA rage
I graduated with a double major in accounting and finance. I did this because I know I don't have the patience for programming

PS - I ran into another 424 error.

Quote:
Private Sub chkOther_AfterUpdate()

'Verify Other capability
If [chkOther] = Yes Then
DLookup("[Other]", "tblUserPCE", "UserID = '" & Forms![frmNewUser]![strCurrentUser] & "'") = Yes
Else: [chkOther] = ""
MsgBox "You may not create a user that can authorize Other issues"
End If

End Sub
If the DLookup returns "No" then there is no error. If the DLookup returns "Yes" then I receive a '424' error.
Posted via Mobile Device
Apoco is offline   Reply With Quote
Old 08-11-2011, 09:22 AM   #12
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

This just in: I'm an idiot.

This form is for admins only. I had forgotten about that. They have access to do everything in the system...so there's no need to limit them.

I never got the controls to work...but I never needed them in the first place. *sigh*
Posted via Mobile Device
Apoco is offline   Reply With Quote
Old 08-11-2011, 09:42 AM   #13
jledou
Have My Own Room
 
jledou's Avatar
14
 
Join Date: Oct 2008
First Name: Jay
Location: Kansas
Posts: 2,201
Trading: (27)
Punch
jledou has a spectacular aura aboutjledou has a spectacular aura aboutjledou has a spectacular aura about
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Just a thought even though you no longer need it Alex, but a couple things to check on the variable - was it dim'd properly, also we run into issues a lot with something above setting a variable/location that it is looking for as something unintended so although you code may be right a run before it sends this string looking in the wrong direction.
My run-ins with VBA though stem from using Excel to interface with a simulation program.
jledou is offline   Reply With Quote
Old 08-11-2011, 12:06 PM   #14
Noodles
Adjusting to the Life
 
Noodles's Avatar
 
Join Date: Oct 2008
Posts: 412
Trading: (2)
HUpmann
Noodles is on a distinguished road
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Quote:
Originally Posted by Apoco View Post
I graduated with a double major in accounting and finance. I did this because I know I don't have the patience for programming

PS - I ran into another 424 error.

If the DLookup returns "No" then there is no error. If the DLookup returns "Yes" then I receive a '424' error.
Posted via Mobile Device
First question, is Yes a global variable in your application?

What datatype is [Other]? Bit, string/text, integer?

Same question with [Hanlding] and [Production]? What datatype are they?

VB and all it's variant is not too clear between take this value and assign to this variable VS is this value equals to this value?

In your case,

Dlookup() = Yes

Access is trying to interpret the above statement and making a guess that you actually want to assign the result of DLookup to the variable Yes (this is a variable, correct? That's why it's telling you to make a switcherroo to Yes = Dlookup().

With regards application security, you should always have some sort of a security in your application, regardless of who will be using them.

If you need help, feel free to send me a message.
Noodles is offline   Reply With Quote
Old 08-11-2011, 02:28 PM   #15
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default

Quote:
Originally Posted by Noodles View Post
First question, is Yes a global variable in your application?

What datatype is [Other]? Bit, string/text, integer?

Same question with [Hanlding] and [Production]? What datatype are they?

VB and all it's variant is not too clear between take this value and assign to this variable VS is this value equals to this value?

In your case,

Dlookup() = Yes

Access is trying to interpret the above statement and making a guess that you actually want to assign the result of DLookup to the variable Yes (this is a variable, correct? That's why it's telling you to make a switcherroo to Yes = Dlookup().

With regards application security, you should always have some sort of a security in your application, regardless of who will be using them.

If you need help, feel free to send me a message.
Other, handling, etc. are all checkbox format (they are a Yes/No value).

Crap. What I was stating for the "then" really should have remained as part of the if statement. If (this box is checked) and (the dlookup shows authorization for the user to check that box) then (nothing? is there a way to say continue as usual?) else (error statement & exit sub). It was completely a formatting issue on my end. I've only been using access for about 2 weeks which is why it probably didn't pop out to me.

The security in place is an in-db login and user rights list. Once the user logs in they are tied to their account which is assigned user, superuser, admin, etc.
Posted via Mobile Device
Apoco is offline   Reply With Quote
Old 08-12-2011, 12:53 PM   #16
Noodles
Adjusting to the Life
 
Noodles's Avatar
 
Join Date: Oct 2008
Posts: 412
Trading: (2)
HUpmann
Noodles is on a distinguished road
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Quote:
Originally Posted by Apoco View Post
Other, handling, etc. are all checkbox format (they are a Yes/No value).

Crap. What I was stating for the "then" really should have remained as part of the if statement. If (this box is checked) and (the dlookup shows authorization for the user to check that box) then (nothing? is there a way to say continue as usual?) else (error statement & exit sub). It was completely a formatting issue on my end. I've only been using access for about 2 weeks which is why it probably didn't pop out to me.

The security in place is an in-db login and user rights list. Once the user logs in they are tied to their account which is assigned user, superuser, admin, etc.
Posted via Mobile Device
Should have been ...

If [chkOther] = Yes AND DLookup("[Other]", "tblUserPCE", "UserID = '" & Forms![frmNewUser]![strCurrentUser] & "'") = Yes then
... true statement here ...
Else
... false statement here ...
End If
Noodles is offline   Reply With Quote
Old 08-15-2011, 06:59 AM   #17
jcruse64
Adjusting to the Life
 
Join Date: Jan 2009
First Name: Joe
Location: Paducah, KY USA planet Earth
Posts: 313
Trading: (1)
jcruse64 is on a distinguished road
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Wish I could help, but haven't really done anything with DLookup in the stuff I built for our lab. I agree Access and VBA can be extremely frustrating at times, but it's still a nice little database program for some applications. Also nice that you can "upgrade" the data to something else like SQL Server when the need finally comes, and hang on to your front end stuff.

I found a pretty decent forum for help at:

http://www.access-programmers.co.uk/forums/index.php
jcruse64 is offline   Reply With Quote
Old 09-16-2011, 02:29 PM   #18
Apoco
Nerd with social skills
 
Apoco's Avatar
4
 
Join Date: May 2010
First Name: Alex
Location: Chattanooga, TN
Posts: 1,286
Trading: (37)
Apoco is a jewel in the roughApoco is a jewel in the roughApoco is a jewel in the rough
Default Re: Anyone here a Microsoft Access pro? [Plea for help]

Next question! (My previous question was resolved...and then destroyed as they changed their mind about what it should do)

What I'm working on now is creating a report filter via query. I know the reporting portion will be used even if the input section is bypassed in favor of direct interaction with SAP.

I have an IIf statement that looks like this as the criteria portion of a query:
Quote:
IIf([Forms]![frmFILTER-rptAllIncidents]![cboCreateUser]<>"",[Forms]![frmFILTER-rptAllIncidents]![cboCreateUser],Like "*")
What I am having an issue with is the "Like "*"" section of the code. I want it to show all entries in the case of a filter being left blank. I have built a form with various combo boxes and text boxes that is functioning...so long as there's an entry in each. If one is left blank it will always return a blank report.

Is there a different way to have it return all fields? I assumed that having "Like "*"" would return all entries due to * being a wild card in MS Office. My google-fu has failed me on this one
Posted via Mobile Device
Apoco is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -6. The time now is 04:17 PM.


Powered by vBulletin® Version 3.7.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
All content is copyrighted jointly by Cigar Asylum and the content provider.