access database search and display

[Shefali]

hello all
i m new to this topic, though I have some experience of using VB to control excel.
I have an access database which has some records. The columns(fields) are first name, lastname, year of birth, city, county, district, state, tel ph number and email id.
Now I want to write a vb code through which the user will search for a person's year of birth(there r multiple people with same year of birth). after this the form shud display the the name of the district in which those persons having the searched year of birth, live. from the list the user wud select the district he wants and the counties within that district shud be displayed. and after selecting a particular county.. the cities within that county shud be displayed.and finally when the user select any of those cities, the email id and the ph number of the matching person(s) shud be displayed.

Comatose.. I have gone thru the link u have given, but cudnt make out much from that. I m really new to this, and any help wud be much appreciated.
thanks.
shefali.

[Comatose]

Do you already have code for this (naturally, not all of it, or you wouldn't be asking) but my question is... have you started the project?

The layout of how I would go about it, is I would make a query for the birth year, and then retrieve all the information about each record for that birth year.....so, if they search by 1979, the database would return a list of records for 1979. Then I would store all the information in arrays for every record in 79, and sift through it in the vb code..... let me know where you are with this, and where I can help.

[Shefali]

thanks for ur reply comatose. actually i havent yet started the project. i am looking around to get some help to get myself started. i m not very sure how do i link access 2003 and vb6.. i mean do i use ado or shud i use the something similar to what is done to link vb and excel.
so once this gets clear to me maybe then i can start with the project.
but i m sure i wud need lots of help.. as i m really new to this.
thanks again.

[Comatose]

Definately ADO. How well versed are you in the VB Language? A lot of times, people who understand little about programming at all want to take on pretty hefty tasks.... I'm not insinuating that you are doing that, I just want to make sure that I don't throw too much stuff in the mix that you haven't yet learned.... that said, I'll give a brief explanation of ADO.

ADO is a bunch of classes that allow you to make an object, so that you can connect to a database. You might only need one of the classes, or all of them. Anyway, the first thing you need to do is tell VB you want to use the database system. So, we go to up to the project menu, then references..... once the new window opens scroll down and find "Microsoft ActiveX Data Objects 2.5 Library" or the "Microsoft ActiveX Data Objects 2.6 Library". If you don't have them, your code will not work. Which means you probably don't have access installed on the box or something. Anyway, moving on.

Once VB knows we want to use those classes, we can make objects of them. So first, we need to define the variables that we will use to work with the database.

Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset

The first line, is going to make a variable that will let us connect to the database. The second one, will allow us to submit a query, and the last one will define how the record should be returned. Then you have to define a connection string. Really this is a pretty lengthy process.

The attachment is a tutorial in code that I found while surfing around, it might be helpful to you.... it's pretty much a skeleton of how to make VB work with ADO

[Shefali]

thank u very much comatose. i will go through the attached file and will try my best to do whatever i can. as far as my vb skills are concerned i m not really very good, but yea i have done some coding and linking vb to other applications like excel and labview.
i hope to get ur guidance if i get messed somewhere.
thanks a lot.

[Shefali]

i have gone thru the tutorial code and also written my code based on that. as of now i can link my database and also retrieve a data of my choice and display it in a text box.
now what i want is that all data in a certain field to be displayed fully and after that the user can search from that list so that additional narrowed down information wud be displayed.
comatose ur help was invaluable to get me upto this... can u plz guide me further?
hoping to get a reply from u or any other members who wud like to help me a bit.
thanks.

[Shefali]

seems no one is interested to give me some suggestions or help me.
anywayz..
i have written a code using ado which connect a access database and searches a particular field and displays relevant information from the search results. for instance if the user searches the fone number, if matches then the persons last name, first name and email id is being displayed.
however i want to narrow down the search, like say the user enters the last name, then all the person matching wud be displayed in a listbox and from that if the user clicks one of those persons, subsequesnt information wud be displayed in another list box and so on.
can someone plz help me in this regard.
thanks.

[Quick-T]

Hi,

I got just that far, and I'm even able to add records, that's no problem. Now I've got the problem that my program can't find them back (properly).

My code (simplified) is:

Private Sub frmMain_Load()

Dim dbReminder As Database
Dim rsBirthdays As Recordset

Do
     rsBirthdays.FindFirst "fieldDate = Date"
     MsgBox((rsBirthdays!fieldName) & " is celebrating his/her birthday today.")
     rsBirthdays.Delete 
'I delete the record, to prevent it from seeing it again, when the
'loop has completed. If i wouldn't do this, it would keep giving a 
'MsgBox, until the date has changed.
Loop

End Sub

The problem is that it gives a MsgBox with every record it can find.
By example: If there is only ONE record in the entire table (with the value "Test" in fieldDate, so just a string), my program thinks its equal to the date.. I tried everything i could find on the web, but it still gives the same problem..

Please help! :S

[supriya_priti]

hi,
i m too new in VB n fasing similar problem i tried to open attachment but failed...can u plz send me extracted 1..if u had open?

thank u very much comatose. i will go through the attached file and will try my best to do whatever i can. as far as my vb skills are concerned i m not really very good, but yea i have done some coding and linking vb to other applications like excel and labview.
i hope to get ur guidance if i get messed somewhere.
thanks a lot.

[vb5prgrmr]

hmmm... not sure... but should that not be fieldDate = " & Date ???

Good Luck

[Quick-T]

Erm, maybe it is.. But its not possible to type:

fieldDate = " & Date

VB makes something like this:

fieldDate = " & Date "

I'm pretty sure you didnt mean to type that.. Must be a typo or something..
So I'm still struggling with the same problem

please help :S

[vb5prgrmr]

No QuickT, I meant what I typed because if you look up to an earlier posting you will see where someone type where = "fielddate = date"...

[supriya_priti]

No QuickT, I meant what I typed because if you look up to an earlier posting you will see where someone type where = "fielddate = date"...

try this: where = "fielddate = '" & data & " ' "

[vb5prgrmr]

Priti, you had better check your quotation marks on that as it will raise an error like it is... and you should see the earlier posting I was mentioning... and you should check which forum your are in...

[Quick-T]

Okay, thanks for help, but it still didn't work, so I tried something else. I thought that maybe, it would be a better idea to create a loop and move to the next record if the date in the DB isnt equal to today's date. Sounds okay, right?

The problem now is that VB crashes when i try to run my program.

Set dbReminder = OpenDatabase(Directory)'yes, "Directory" is okay
Set rsReminderBirthdays = dbReminder.OpenRecordset("tableBirthdays", dbOpenDynaset)

If Not rsReminderBirthdays.EOF Then
    rsReminderBirthdays.MoveFirst
End If

Dim MakeLoopInfinite As Boolean
MakeLoopInfinite = False

Do While MakeLoopInfinite = False
    
    If rsReminderBirthdays!fieldDate = Date Then
    
        Box = MsgBox((rsReminderBirthdays!fieldDescription & " is celebrating his/her birthday today! " & rsReminderBirthdays!fieldDescription & " became " & (Year(Now) - rsReminderBirthdays!fieldYear) & "."), vbOKOnly, "Birthday", vbCritical)
        rsReminderBirthdays.Delete
        If rsReminderBirthdays.BOF Then
        
            rsReminderBirthdays.MoveFirst
            Else: rsReminderBirthdays.MoveNext
            
        End If
    End If

Loop

End Sub

Please help again :S..

[vb5prgrmr]

On which line does it crash? I don't see where you check for EOF to set your loop flag to true. Make your loop do while not rs.eof and that should solve your problems.

Good Luck

[Quick-T]

well, i don't know! It's not an error or something.. even with breakpoints VB crashes.. not just my program, but VB.. I have to shut it down after running my program.

[vb5prgrmr]

Okay, first, in tools>options make sure require variable declarations is checked>ok. Then, at the top of each code module put option explicit as the very first line. Next, save and then press F8 to see if any errors are brought to your attention. Take care of those errors and add error handling to everything!!! And no that does not mean adding on error resume next, it means adding actual error handlers even if it is just as simple as I do with a msgbox "ProcedureName " & err.number & ":" & err.description. Then you will know where your errors are, what they are, and can figure out, with or without our help, how to fix them.

Good Luck

[Quick-T]

thanks vb5prgrmr! i figured it out,