ok, here's a simple one

[mjmartin]

I have a form called 'Form_show_login'

Inside is two textboxes, one called ownerName and one, ownerPassword
Also is a button called 'frmlogin'

I have two tables, one called DBOwners which has the fields, DBOwnerID (autonumber), DBOwnerName, DBOPass, DBGroupID (f-key) and DBAdmin (bool)

And one called DBGroups (DBGroupID, DBGroupName and DBGEmail)

when I click the button I want to do something like:

sql = "SELECT DBOwnerID, DBGroupID, DBAdmin FROM DBOwners WHERE DBOwnerName='" & ownerName.value & "' AND DBOPass='" & ownerPassword.value & "'"

it should only return one record (because DBOwnerName is unique). My question is, how do you get VBA to do this? I want a function to return True if a record is returned (a record would return if the username and password match-up) and False if no data is returned.

Any ideas?

 

[KenHigg]

dlookup()

- Looks like you're re-inventing the Access secruity model?!?

???
ken

 

[mjmartin]

dlookup, I'll look into that, thanks.

I'm not using the Access security model because I'm only using Access as a FrontEnd to a MySQL database, the Access Front End will then be compiled down using Access Extensions (Visual Studio Tools for Microsoft Office 2003) I'm therefore deliberately avoiding the Access security model. I'd be happy to show you the project if you're interested. I used a bit of a hack (in AfterUpdate) to work around the MyODBC #Deleted# problem but it works pretty well.

 

[mjmartin]

The code I'm using for the login click button is:

Dim varX As Variant
varX = Access.DLookup("[DBOwnerID]", "DBOwners", "[DBOwnerName] = " & ownerName.Value)
MsgBox varX

Unfortunately when I run that code, Access says: Runtime Error: 2001. You cancelled the previous operation

Any thoughts?

 

[KenHigg]

Did debug stop here when you stepped through it?


ken

 

[mjmartin]

Yeah, it stopped on the dlookup line.

That's all the code in the function btw. Database connects btw so I know it's not an ODBC related issue.

 

[KenHigg]

varX = Access.DLookup("[DBOwnerID]", "DBOwners", "[DBOwnerName] = " & ownerName.Value )

Stepping through the code, right before the error, is ownerName.Value giving the correct value? What is this referring to?

 

[mjmartin]

It's the value of a textbox. I stepped through the code and that works. I just typed 'mike' in the textbox and even added a msgbox just to make sure it's assigned and it is.

The problem's definitely on the dlookup line.

 

[KenHigg]

varX = Access.DLookup("[DBOwnerID]", "DBOwners", "[DBOwnerName] = '" & me!mytextbox & "'" )

 

[mjmartin]

yeah I'd just seen that. Doh!!!

varX = Access.DLookup("[DBOwnerID]", "DBOwners", "[DBOwnerName] = '" & ownerName.Value & "'")

Fixed it. Thanks for the help. My cheesing authentication system is now place

 

[KenHigg]

Q. Do you need the Access qualifier in the Access.DLookup() to compiled down using Access Extensions? Do you have a good web read on the subject? (Never done this)

???
ken

 

[mjmartin]

Nah you don't need the qualifier, just force of habit on my part.

Here's an MSDN doc on making an msi with the runtime and mde

For simple packages all you need to do is make an MDE file and use the extensions to create a setup package. Access Extensions give you a royalty free distribution right to the Access Runtime. This means that you simple make the MDE file, run the packaging wizard and then give it to your customers. With Access Runtime, customers don't need Access installed on their computer. That saves a ton on licensing.

 

[KenHigg]

How would you test it? Would you have to re-install the os every time or is there a list of reg settings to reset the computer back to a clean system?

ken

 

[mjmartin]

I tend to use Ghost for all my tests because I don't trust uninstallers. So yes, for testing you'd have to re-install the os everytime unless you use an image program like ghost. I still think Access Extensions rock though because the end user doesn't need to have Access installed.

Here's another problem if you're interested...

I have a textbox (which is a field in every table) called 'RecordOwnerID'

Every time a record is updated, I want to set the RecordOwnerID.Value (i.e. the field called RecordOwnerID) to the same value as a variable I'm using in memory called 'CurrentOwnerID' (CurrentOwnerID is a public Integer)

So basically, before a record is updated I want to do:

RecordOwnerID.Value = CurrentOwnerID

I can't figure out how to do it, any ideas?

 

[KenHigg]

Is RecordOwnerID a key of any kind?

ken

 

[KenHigg]

In the form beforeupdate event:

me!RecordOwnerID = CurrentOwnerID

Would this work?
ken

 

[mjmartin]

RecordOwnerID is a Foreign Key.

It's basically logs who created the record.

 

[KenHigg]

In the form beforeupdate event:

me!RecordOwnerID = CurrentOwnerID

Would this work?
ken

Did this work?
ken

 

[mjmartin]

yeah it worked, sorry I didn't get back to you later. Thanks

I'm a PHP guy, last time I used VB was 2 years ago so I'm still a little rusty and that was VB6, Me!ObjectValue looks like a VBA / VB difference, in fact if I recall correctly, the 'Value' property doesn't exist for textboxes in vb6, I always used 'textbox.Text' instead. Plus the amount of times I've typed:

if (text.value == "whatever") {
//dostuff
} else {
//dostuff
}

in this project where I should have used VB just isn't funny

 

[KenHigg]

Cool.

I think the engine for this forum is php/mysql. What the main diff between say, ASP and PHP?

ken