Query Results = 0 Then Add New

[Friday]

I have a file that users input background investigation data into. It is a simple, flat file that is not related to any other data. Currently, from the main menu, they just click a button and a form appears with a list box populated by a query showing all the records sorted in last name. They scan this list to see if the record they have already exists (the list shows last name, first name and SSN). If not, the form has a button on it that takes them to the entry screen. If the record exists, then they can click on that line in the list box, which brings up that record, and they can check to see if any updates are necessary. The file is getting a little big for this type of process. What I would like to do is this: From the main menu, present the user with a prompt for the subjects last name, which is presented to a query. Easy. If the last name exists in the table, a listbox populated with all the records containing the matched last name appears, allowing the user to check for his record. if the last name does not exist in the table (rowcount =0), go straight to the blank data entry form. I seem to have trouble with the sequence of events, so I am looking for design ideas here. Case statement? Sorry this is so wordy.

 

[yippie_ky_yay]

Hi Friday - I see what you mean about this! I thought it would be fairly simple, but ran into problems. Here's the closest I could get (but now I have to go back to work) - I wanted to go "rst.RecordCount = 0" but obviously I need to learn a bit more about it!

So, button on your main form:

Private Sub CommandButton_Click()
Dim rst As New ADODB.Recordset
Dim qryGetName As String
Dim txtName As String

txtName = InputBox("Enter your name:")
qryGetName = "SELECT tbltable.name FROM tbltable WHERE (tbltable.name=""" & txtName & """);"

rst.Open qryGetName, CurrentProject.Connection

If rst.EOF Then
MsgBox "Open the empty form (or turn off the filter - whatever it is you do)"
Else
MsgBox "Open form to the record(s)"
End If

End Sub

I based it on the fact that if the current record is not EOF then none must exist. I don't like relying on that, but hopefully this might get you started. Let me know what you end up doing!

-Sean

 

[dcx693]

The only way you could be simultaneously at the beginning and end of a recordset is if the recordset is empty. Use code like this to check:

If rst.BOF And rst.EOF Then
   'recordset must be empty
End If

 

[yippie_ky_yay]

Thanks dcx693, I plugged away at what I posted above until it worked.

In my head, "rst.RecordCount=0" makes logical sense - can you tell us why it doesn't work? In testing, I found that rst.RecordCount was always "-1" - wether it found any records or not.

-Sean

 

[Rich]

Why not use a combo box and the not in list event?

 

[dcx693]

yippie_ky_yay, forward-only type recordsets in ADO always return -1 for the recordcount. The default recordset type for ADO is forward-only and read-only (also among the fastest). The recordcount should be accurate for non-forward-only types, but I haven't tested it out.

 

[WayneRyan]

Sean,

You have to do a .MoveLast to get the .RecordCount, at least
with DAO. Just get your code in the debugger when you know
you have some records and look at .RecordCount.

Wayne

 

[pono1]

ADO has different cursortypes that affect how you can interact with the data -- the default, as dcx wrote, is adOpenForwardOnly.

There are two cursorTypes that do support the recordcount property -- adOpenStatic and adOpenKeyset -- and two other cursors -- adOpenForwardOnly and adOpenDYnamic -- that don't.

Dim rst as new adodb.recordset

With rst
	.source = "TblWhatEver"
	.ActiveConnection = CurrentProject.Connection
	.Cursortype = adOpenKeyset
                .open

	msgbox .recordcount

End with

Regards,
Tim

 

[yippie_ky_yay]

Thanks everyone! - sorry to Friday though (I feel like I took over your thread)

-Sean

 

[Friday]

Thanks for all the input, folks. I wil be working on this again tommorow and will let you know how it came out. Thanks again!

 

[billyr]

Dumb question. Why not use 2 cascaded CBOs. The first one with a UNIQUE list of last names. The second with a list of (if any) last names matching the one selected from the first. Bound column of second should be SSAN? First name? The notinlist event from either CBO could be serviced by the same data form.
The source for each CBO could then be stored querys. The last name field of CBO#2 would have its criteria set to - like forms!yourformname!yourCBO#1name. The source for CBO#1 is simply a unique list of last names.