Hi, My Name Is Nubski

[tulsafork]

Hi guys this is my first post on DaniWeb, I have searched for help on here for hours on hours.
I am the webmaster at my high school and I have been given the project of making a Book Depository for one of our departments, one that would do the same duties of Follet, check in check out input data view data ect.
I have a Access database with the information that I need on it. It is in 2000 format I believe. I understand that I need to reference to DOA which i have done. I started trying to make a login page and that even after reading the many pages on them in here I am still stumped.
Right now I dont have any code. I decided to try to start fresh. I guess I am asking for help as in sudgestions on where to start and once I do start what is after that?
I have never worked in Visual Basic before. I have done a little at home on my Mac using X code. That may be my next option if this doesnt work out. Thanks in advance if anyone can help me.

[Prozeen]

Welcome to the forum Nubski.

Does this help?

[cfry]

Hi Nubski,

A few questions:
1. Will this application run on multiple computers?
2. Are your users staff, students or both? I.e. What level of trust can you assume from your users?
3. Do your users have individual Windows accounts (I assume a Windows environment because you mentioned Access and Visual Basic)? If so, are they local workstation accounts or accounts on a domain?
4. You mentioned Access - are you keen to build your system using Access or is it just the format your source data is in?

Sorry for all the questions - I'll try to reply quickly if you answer them.

Regards,

Chris Fry
Canberra, Australia

[tulsafork]

-This will run on multiple computers for now. Later we may run it from my computer throughout the network.
-Users will be staff only.
-Users do have individual windows accounts on a domain
-I have been using access because I took a class on it and am familiar with it.

No worries on the questions, any kind of help with this would be greatly appreciated.

Prozeen thanks for that link. I have had a chance to look at it and it looks like its going to help. Im going to dive into it now.

-Derek DeJonghe

[cfry]

Ok, this is a long post, sorry about that... These are instructions on how you can build a fairly secure, easy to use network database system in Access with back/front end architecture. This means your data will sit in one place, but can be accessed from multiple workstations.

I'm using Access 2003 - sorry, I don't have a copy of 2000 at the moment if that's what you're using.

1. Create a folder on your network called something like "Book Depository". Within that, create a folder called "Database", one called "Interface" (or "Back" and "Front" if you prefer...) and I usually put a third called "Backup" so I can keep time/date labelled, zipped archives of the files to roll back to on a regular basis in case I mess something up (don't underestimate how useful this is... database development by nature saves pretty much everything as you do it - accidents happen)

2. Create a group on the domain called "Book Depo Users" or similar for all the people who should have access to the application. Once that's done, add the required users to the group.

3. Grant and limit access to your "Book Depository" folder to the "Book Depo Users" group.

4. Either put your existing Access 2000 database into the "Database" folder, or make a new Access database and merge the existing data into it as required. Let me know if you want help with the data structure. Do you know about "Database Normalization"? If not, it's pretty important and can will save you a lot of grief down the track. It's best to set up your data structure properly at the start. Let me know if you need help with that.

5. Create a new folder in the "Interface" folder called something like "Book Depository". You can use something more snappy if you want to give your application an interesting name ;-)

6. Create a new Access Database file in the "Book Depository" folder and call it "Book Depository.mdb"

7. Open your new Access Database File if you haven't already and check you are in the Tables window.

8. Right click the white space in the Tables windows and select "Link Tables...". This is the crucial step in linking your Interface to your Database.

9. Navigate to your "Database" folder and choose the Access file with your data in it. To make installation easier, try to navigate to the folder using a network path that will be available to all Book Depository Users.

10. Once you've chosen your database, you should see a list of tables. Click Select all, then click OK.

11. The next part is to write your forms, queries, macros, modules and reports. There are some tricks you can use - search Access Help for "AutoForm", then open the "Create a form" link. That's an easy way to turn tables into forms for example.

12. Another tip - if you create a macro called "autoexec", it will execute when the database is opened. I use this to open a "main switch" form when the user starts the database. There's lots of other little tricks you can use like automatically maximising that form, changing the background so it looks prettier than just grey, and disabling the Tables, Queries, Forms... window by default (unless you open the application holding shift), but that stuff probably belongs in an Access forum.

13. When you are ready to distribute your first release, copy the "Book Depository" folder to your user's workstations into the "Program Files" folder (normally C:\Program Files)

14. Grant and limit access on the '..Program Files\Book Depository' folder to the "Book Depo Users" group and replicate the permissions down

15. Open the Access file to check the tables have linked properly. If they haven't, go into the tables window, delete all linked tables and re-link them. Once you get this right on one computer, keep that file on the network - chances are it'll work on the others. Your queries, forms etc. should all work after that.

16. Once you're happy with the database, put a shortcut to "C:\Program Files\Book Depository\Book Depository.mdb" onto either the user's desktop if you want to install the application for one user, or the "All Users" desktop if you want to install it for every user on that computer. Name the shortcut something nice like "Book Depository". I like to change the icon too, but that's up to you.

And there you have it. You now have a front/back, network database configuration with basic user security!

Also, with installation, once you get comfortable with it, you can create an executable installer using a program like "Installer2Go", available here: http://www.dev4pc.com/installer2go.html. The only catch with the free version is that at the end of the installation there'll be one more screen telling you about Installer2Go with a link to the website (that's actually how I found it, so I don't mind personally).

I know there's a lot of info there, so post again if you have questions about how to do certain things more specifically.

Hope that helps,

Chris Fry
Canberra, Australia

[tulsafork]

I have done that, what I am working on is a program that teachers can open, input and receive data, without having to know anything at all about the system they are using. Im sorry that you misunderstood and typed all of that. I think this project is near done, the tutorial posted by Prozeen helped a ton, now I just need to search for small code hints.
Thanks
-Derek

[cfry]

No probs, I figured it may be useful for Googlers looking for that sort of thing anyway.

Any code problems in particular I can help you with?

Chris

[tulsafork]

If you could help with this that would be great.
I have started to get a understanding in VB but this has me stumped,
Check out the picture of one of the forms I have attached.
I am having trouble with a find button, I want to be able to have the text for a barcode put into the bartxt textbox and then have the information come up in the other boxes.
I have it so that the information will populate the text boxes if you click a barcode. I would like to not have a find button at all and have it so that if the text is changed to a existing barcode then the information will populate. We are hoping to use a scanner for teachers because all students have ID's with barcodes, we want to scan the book have the information come up click the student ID textbox scan their ID and click update. What I am saying is I dont want them to have to press a find button if I can find the code for that.
here is the code I have now for this form, findcmd_Click is where the problem is, I tried to use bartxt_Change but that didnt work out so i tried a find command. think you could help with this?

Dim dbMyDB As Database
Dim rsMyRS As Recordset


Private Sub findcmd_Click()

rsMyRS.FindFirst "BARCODE=" & Str(bartxt.DataField(bartxt.Text))
titletxt.Text = rsMyRS!Title
studenttxt.Text = rsMyRS!StudentID & ""
isbntxt.Text = rsMyRS!ISBN
availabletxt.Text = rsMyRS!Available

End Sub

Private Sub Form_Load()

Set dbMyDB = OpenDatabase("C:\Documents and Settings\webmaster\My Documents\Bookdepository\inventory_database.mdb")
Set rsMyRS = dbMyDB.OpenRecordset("BARCODES", dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirst

Do While Not rsMyRS.EOF
    records.AddItem rsMyRS!Barcode
    records.ItemData(records.NewIndex) = rsMyRS!Key
    rsMyRS.MoveNext

Loop

End Sub

Private Sub records_Click()

rsMyRS.FindFirst "Key=" & Str(records.ItemData(records.ListIndex))
titletxt.Text = rsMyRS!Title
studenttxt.Text = rsMyRS!StudentID & ""
isbntxt.Text = rsMyRS!ISBN
availabletxt.Text = rsMyRS!Available
bartxt.Text = rsMyRS!Barcode


End Sub

Private Sub updatecmd_Click()

rsMyRS.edit
rsMyRS!StudentID = studenttxt.Text
rsMyRS!Available = availabletxt.Text
rsMyRS.Update
End Sub

[cfry]

Ok, forgive me if I miss anything, but I think I get your code (always a bit tricky without running it).

Does the find button work? If so, firstly, I'd put the contents of the findcmd_Click function into another sub like this:

'Find the supplied barcode and populate the
'  text boxes
Private Sub find(ByVal barcode As String)

  rsMyRS.FindFirst "BARCODE=" & barcode
  titletxt.Text = rsMyRS!Title
  studenttxt.Text = rsMyRS!StudentID & ""
  isbntxt.Text = rsMyRS!ISBN
  availabletxt.Text = rsMyRS!Available

End Sub

Then, change your findcmd_Click function to:

Private Sub findcmd_Click()

  Call find(Str(bartxt.DataField(bartxt.Text))

End Sub

Finally, write an AfterUpdate() function for the bartxt box like so:

Private Sub bartxt_AfterUpdate()

  Call find(Str(bartxt.DataField(bartxt.Text))

End Sub

You'll want bartxt to have the focus as soon as the form loads, to avoid your barcode scanner putting entries in other text boxes. Some barcode scanners automatically send the <Enter> key after the data, which would mean as soon as the barcode was scanned the text boxes will update, but alternatively, the user could hit <Enter> or <Tab> after scanning and that should update the data too.

Also, I'm aware of the redundant line:

Call find(Str(bartxt.DataField(bartxt.Text))

You could put this in your find() sub if you like in stead of the barcode parameter, but if you want to reuse find() for anything else down the track, this may cause more work :-).

This is untested code, so apologies in advance for any errors.

Does that answer your question?

CF