Programming (VBA) help.

[Llama]

I need to write some code in VBA and the internet is useless.

I have a database with various tables, one called ' tblWorkset'
In it are various columns such as "WorksetID" "TeacherID" "ClassID"

I want to write some code that if I click a button on a form, it searches based on a text box that's also in that form.

Anyone know how I'd do this? I have no idea of the syntax of VB / VBA.



I also have an algorithm for a log in procedure but I have no idea how to turn it to VBA.

Input LogID
Input Password

Select Usermode (Teacher/Pupil)(Could do 2 sep login forms i dont care)
IF Usermode = Student then StudentTbl = assigned table.
Else TeacherTbl = assigned table.

Endif

IF UserID = Assigned UserID. THEN
IF Password = Assigned Password THEN open menu form.
Else "Password Incorrect" clear form,
Else "UserID incorrect" clear form,
End IF
End IF.

Any ideas for either of these, guys & gals?

 

[No Limit]

I have a hard time believing you can't find simple tutorials or code snippets for doing database connectivity. I haven't worked with VBA but found this after a 15 second google search:

http://vbaexcel.eu/vba-macro-code/d...tabase-querying-data-into-excel-using-vba-dao

Is this for a school project? What kind of database is it?

 

[TimoJay]

I need to write some code in VBA and the internet is useless.

Sooo... you insult us, then you want us to do your work for you?

nice

 

[Llama]

No Limit: It's all being done in Access, but thanks, I shall have a look.

Timjay: Use your brain, if you have one. Obviously I hasn't explicity insulting the people on this website, though I don't have a problem pointing out how limited your faculties must be.

 

[StarBob]

I'm not 100% clear on what you're trying to accomplish, but from what I've gathered here's how I'd do it.

You have a Student table and a Teacher table with whatever information you're trying to get from them. Then you have a universal login table with username, password (normally hashed and encrypted), and login type.

The first portion, for login, would be something like -

*OnButtonClick method*
Dim SQL as String
SQL = "SELECT * FROM Users WHERE Username = @UserName and Password = @Password"
SQL = FormatSelect(SQL)
MsgBox SQL

If UserType = 1 then
*Load Student Page*
else if UserType = 2 then
*Load Teacher Page*
end if

I don't know a lot about using SQL directly in VB because I use VB.NET and stored procedures in SQL Server exclusively.... so the SQL syntax may not be right. I'm sure you can problably use them in access as well, but they're a little more complicated than just throwing the query in the code. But yeah I'm not sure about the syntax off the top of my head but I'm sure it's out there.

 

[Llama]

That's cool i'll give it a try

On the search, I currently have a simple bit of code

Private Sub RecordFind_Click()
DoCmd.OpenForm "Searchform1", , , "ClassID=Forms!SearchMenu!classcode"

This works fine, it filters data in the second form based on 'ClassID' which I put in the first form.
Now, I try the same where the second form has data from two tables (StudentID aswell), and try the following

Private Sub RecordFind_Click()
DoCmd.OpenForm "Searchform1", , , "ClassID=Forms!SearchMenu!classcode" And "StudentID=Forms!SearchMenu!studentcode"
End Sub

But this gives me an error (Runtime Error type 13, type mistmatch)

Any idea what the problem is there? I have a feeling it's to do with the fact my second form draws from two tables so is now a form and then subform.

 

[StarBob]

I'm not sure but it looks like the OpenForm command only takes one paramter... I don't know the syntax,but would the "And..." go inside the quotes?

 

[Llama]

Perhaps...would it work better (if at all) to use SQL to create a view that I search from rather than from the DB?

Also, more refinement on the login. (I'm a total newb at this btw)
Student and Teacher Table each have their respective ID's and passwords. (Before they can log in at all they need to log in with a general group login which restricts what they can access and see anyway, so I could simply set up a slightly different form for each usergroup)

I'm thinking that on my login form, the boxes for ID and password have a corresponding (and hidden) set of boxes which load the data from the database, and then check against it. Only problem is I'd have no idea how the database would select the ID / password combination in the first place. Any ideas?

 

[StarBob]

Perhaps...would it work better (if at all) to use SQL to create a view that I search from rather than from the DB?

Also, more refinement on the login. (I'm a total newb at this btw)
Student and Teacher Table each have their respective ID's and passwords. (Before they can log in at all they need to log in with a general group login which restricts what they can access and see anyway, so I could simply set up a slightly different form for each usergroup)

I'm thinking that on my login form, the boxes for ID and password have a corresponding (and hidden) set of boxes which load the data from the database, and then check against it. Only problem is I'd have no idea how the database would select the ID / password combination in the first place. Any ideas?

Creating a view would work well if you're pulling things from multiple tables to search.

You shouldn't need an initial log in to determine their view. Your query and application should determine what data they receive. Depending on the individual's logon it should display their individual information. Too bad you're not using VB.net with this though, it would be a lot easier for you to just usea gridview to bring up data you want.

 

[Llama]

true. I'll try using a view then ^^
Still not sure about the password thing though =[.

 

[TimoJay]

Strange... He mistook my statement of reality as an insult and then he feels the need to lash out at me with completely groundless remarks. I understand this is most likely a result of internet anonymity combined with the fact you most likely composed your post moments after reading mine. (L O L)

Though I do admit I don't know squat about VBA, because well........................ it's VBA.

 

[Llama]

Your 'statement of reality'? You evidently haven't got the brainpower to read my first post and realise I wasn't being specific and wasn't insulting anyone. It's not my fault you got butthurt over your own inabilities with the english language. Perhaps you should go back to school, or step out of mommy's basement once in a while - it might do you some good.




If Starbob / No Limit etc comes back and reads this,
DoCmd.OpenForm "Searchform", , , "DateSet=Forms!searchbysetdate!setdate"
If DateSet <> setdate Then
MsgBox "No matching records", 0, "search failed"
Exit Sub
End If

Returns the message box when I search for a value that is definately in the database, any idea why? I'm pretty sure Syntax is correct.
Input on that would be appreciated.

 

[Mr-Fusion]

I think you're missing an end if, it looks like it's exiting the sub before the if statement can complete?

If DateSet <> setdate Then
MsgBox "No matching records", 0, "search failed"
Exit Sub
End If

should be

If DateSet <> setdate Then
MsgBox "No matching records", 0, "search failed"
End If
Exit Sub

 

[No Limit]

Your 'statement of reality'? You evidently haven't got the brainpower to read my first post and realise I wasn't being specific and wasn't insulting anyone. It's not my fault you got butthurt over your own inabilities with the english language. Perhaps you should go back to school, or step out of mommy's basement once in a while - it might do you some good.

I think he was originally joking and you took it way too personally.

 

[Llama]

MrFusion: Thanks, I'm an idiot.
Also, jokes have to be funny to be jokes. Too many people have forgotten that. =[

 

[Letters]

And this is why we keep the programmers locked up. Somebody get this guy outta here.

 

[TimoJay]

ohhhhhhh