That's the message I get now. Here's what I changed:



Removed the single quotes from the code, & change the form properties for 

Data Entry from Yes to No.



I think the Data Entry property was part of the previous problem (no 

current record). I'd like to leave the form to open as data entry if 

possible, but still be able to search for a record?



+Tammy

You may want to consider the test:



If rs.eof and rs.bof then

    'If both eof and bof are true there are no records in the recordset

endif

Would this be used if I wanted to keep the form as opening in data entry, but still being able 

to find the records? Or would this just solve the syntax problem?



+Tammy



> You may want to consider the test:

> 

> If rs.eof and rs.bof then

>     'If both eof and bof are true there are no records in the recordset

> endif

> 

> 

Tammy,



The test for an empty recordset will allow you to branch in your code based

on whether or not there were any records in it. It's good practice to

anticipate likely occurrences and provide for their resolution. For example,

if your query does not return any records you could pop up a msgbox

informing the user that there is nothing to do.



Hope this helps,



Walt

Actually, I'm kind of confused (like I've said before, I'm VERY new at code!). There should never 

be an occurence of no return, because the user will be selecting a name from a drop down list; 

the list is created from the table that the form writes to, so as a new contact is added via the 

form, the info goes to the table, & the contact is added to the drop down list. I'm just trying to 

get the drop down list to work - always has before, with the same code!



+Tammy



> Tammy,

> 

> The test for an empty recordset will allow you to branch in your code 

based

> on whether or not there were any records in it. It's good practice to

> anticipate likely occurrences and provide for their resolution. For 

example,

> if your query does not return any records you could pop up a msgbox

> informing the user that there is nothing to do.

> 

> Hope this helps,

> 

> Walt

> 

> 

Tammy,



Sorry if I've misunderstood the question. Are you attempting to access your

table with DAO or ADO directly or through SQL? If DAO (which is what I use)

the .nomatch property can be checked. Are you seeking based on an index? If

so, are your keys correctly stated?



If you can post the snippet of code that is causing the problem perhaps

someone may be able to offer further help.



Again, I apologize if I've been the source of confusion.



Regards,



Walt

[quick point of procedure--please post the text of error messages in

addition to their numbers--not everybody who might be inclined to chime in

will have time to dig that up.]



Setting the .DataEntry property to True tells Access that you want that form

to be good *just* for data entry--you don't want to see any of the existing

records (this comes in handy sometimes when the tables are waaaay large).

To allow data entry in addition to browsing existing records you use the

.AllowAddtions prop.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...]

Sent: Monday, November 19, 2001 12:44 PM

To: Access

Subject: [access] Run Time Error 3077 instead of 3021





That's the message I get now. Here's what I changed:



Removed the single quotes from the code, & change the form properties for 

Data Entry from Yes to No.



I think the Data Entry property was part of the previous problem (no 

current record). I'd like to leave the form to open as data entry if 

possible, but still be able to search for a record?



+Tammy

Walt - don't feel bad!! Remember - I'm the one new to VBA!!



Roy - first, let me say that I learned Access w/out using Code, knowing that the code would 

make it only better, but there weren't classes yet available for that (now my job can't afford it! 

I work for the State of Florida, & we've been hit hard in the revenue area because of recent 

events).



Okay, so I'm using the Properties box on the form, not the VBA; I have Data Entry as yes, & 

Allow Additions as yes.



The form is for filling in the info on people who have contacted a ministry (no, this db isn't job 

related!). In the form header I've placed the same type of drop down box I've used on other 

dbs, that allows the user to search for a particular record to make changes. The list is based 

on a select statement that pulls the first & last name of each contact & puts them in the list; 

the SQL is: SELECT [LastName] & ", " & [FirstName] AS Name, [tblContact].[LastName] FROM 

tblContact ORDER BY [tblContact].[LastName]; 



Then, I added code to the drop down box so that, when a name was selected, it would display 

that record (allowing for updates if needed). That code is (Member ID is the primary key):



Private Sub MemberSearch_AfterUpdate()

    'Find the record that matches the control.

    Dim rs As Object

    

    Set rs = Me.RecordsetClone

    rs.FindFirst "[MemberID] = " & Me![MemberSearch] & ""

    rs.Bookmark = Me.Bookmark

End Sub



When I try to use the box, I get the Run Time error 3021: No Current Record.



If I change the form to Data Entry = No, I get the Run Time error 3077: Syntax error (comma) 

in expression.



Kind of like, "darned if I do, darned if I don't" !!!



+Tammy



(ps - Roy, I'm not only impressed with your credentials, I'm honored that you consider all of our 

problems worth your time!! Thanks.)



> Setting the .DataEntry property to True tells Access that you want that 

form

> to be good *just* for data entry--you don't want to see any of the 

existing

> records (this comes in handy sometimes when the tables are waaaay large).

> To allow data entry in addition to browsing existing records you use the

> .AllowAddtions prop.

> 

> HTH,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

I suspect you're not passing FindFirst the value you expect.  If you put the

line



   MsgBox "Getting ready to search for the MemberID " & Me!MemberSearch



right before the call to FindFirst, what do you see?



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...]

Sent: Tuesday, November 20, 2001 8:59 AM

To: Access

Subject: [access] RE: Run Time Error 3077 instead of 3021





Walt - don't feel bad!! Remember - I'm the one new to VBA!!



Roy - first, let me say that I learned Access w/out using Code, knowing that

the code would 

make it only better, but there weren't classes yet available for that (now

my job can't afford it! 

I work for the State of Florida, & we've been hit hard in the revenue area

because of recent 

events).



Okay, so I'm using the Properties box on the form, not the VBA; I have Data

Entry as yes, & 

Allow Additions as yes.



The form is for filling in the info on people who have contacted a ministry

(no, this db isn't job 

related!). In the form header I've placed the same type of drop down box

I've used on other 

dbs, that allows the user to search for a particular record to make changes.

The list is based 

on a select statement that pulls the first & last name of each contact &

puts them in the list; 

the SQL is: SELECT [LastName] & ", " & [FirstName] AS Name,

[tblContact].[LastName] FROM 

tblContact ORDER BY [tblContact].[LastName]; 



Then, I added code to the drop down box so that, when a name was selected,

it would display 

that record (allowing for updates if needed). That code is (Member ID is the

primary key):



Private Sub MemberSearch_AfterUpdate()

    'Find the record that matches the control.

    Dim rs As Object

    

    Set rs = Me.RecordsetClone

    rs.FindFirst "[MemberID] = " & Me![MemberSearch] & ""

    rs.Bookmark = Me.Bookmark

End Sub



When I try to use the box, I get the Run Time error 3021: No Current Record.



If I change the form to Data Entry = No, I get the Run Time error 3077:

Syntax error (comma) 

in expression.



Kind of like, "darned if I do, darned if I don't" !!!



+Tammy



(ps - Roy, I'm not only impressed with your credentials, I'm honored that

you consider all of our 

problems worth your time!! Thanks.)



> Setting the .DataEntry property to True tells Access that you want that 

form

> to be good *just* for data entry--you don't want to see any of the 

existing

> records (this comes in handy sometimes when the tables are waaaay large).

> To allow data entry in addition to browsing existing records you use the

> .AllowAddtions prop.

> 

> HTH,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

Do I put the Data Entry to yes or no?



+Tammy



(What does HTH mean?)



> I suspect you're not passing FindFirst the value you expect.  If you put 

the

> line

> 

>    MsgBox "Getting ready to search for the MemberID " & Me!MemberSearch

> 

> right before the call to FindFirst, what do you see?

> 

> HTH,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

> -----Original Message-----

> From:	Tammy Tappan [SMTP:GRTappan@e...]

> Sent:	Tuesday, November 20, 2001 3:36 PM

> To:	Access

> Subject:	[access] RE: Run Time Error 3077 instead of 3021

> 

> Do I put the Data Entry to yes or no?

> 

> +Tammy

> 

> (What does HTH mean?)

> 

> > I suspect you're not passing FindFirst the value you expect.  If you put

> 

> the

> > line

> > 

> >    MsgBox "Getting ready to search for the MemberID " & Me!MemberSearch

> > 

> > right before the call to FindFirst, what do you see?

> > 

> > HTH,

> > 

> > -Roy

> > 

> > Roy Pardee

> > Programmer/Analyst

> > SWFPAC Lockheed Martin IT

> > Extension 8487

> 

> 

Data Entry should be No--you want to be able to browse existing records in

the Form's data source (unless I've misunderstood you).



Cheers,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...]

Sent: Tuesday, November 20, 2001 12:36 PM

To: Access

Subject: [access] RE: Run Time Error 3077 instead of 3021





Do I put the Data Entry to yes or no?



+Tammy



(What does HTH mean?)



> I suspect you're not passing FindFirst the value you expect.  If you put 

the

> line

> 

>    MsgBox "Getting ready to search for the MemberID " & Me!MemberSearch

> 

> right before the call to FindFirst, what do you see?

> 

> HTH,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

Okay, I did it - the message box came up, I pressed "Okay", & then the same run-time error 

came up (syntax problem).



+Tammy





> 

> > I suspect you're not passing FindFirst the value you expect.  If you 

put 

> the

> > line

> > 

> >    MsgBox "Getting ready to search for the MemberID " & Me!MemberSearch

> > 

> > right before the call to FindFirst, what do you see?

> > 

> > HTH,

> > 

> > -Roy

> > 

> > Roy Pardee

> > Programmer/Analyst

> > SWFPAC Lockheed Martin IT

> > Extension 8487

> 

> 

Hi Tammy



Roy's point is that you seem to be passing the wrong value to your code.



You say that the combo box rowsource is:



SELECT [LastName] & ", " & [FirstName] AS Name,

[tblContact].[LastName] FROM 

tblContact ORDER BY [tblContact].[LastName]; 



This populates the combobox with a list of names. Your code however is 

expecting a Member ID, which, presumably, is not the name of the member?



Private Sub MemberSearch_AfterUpdate()

    'Find the record that matches the control.

    Dim rs As Object

    

    Set rs = Me.RecordsetClone

    rs.FindFirst "[MemberID] = " & Me![MemberSearch] & ""

    rs.Bookmark = Me.Bookmark

End Sub



I think you need to change a few details of the combobox to get everything 

to work. Change the rowsource to:



SELECT [MemberID],[LastName] & ", " & [FirstName] AS Name,

[tblContact].[LastName] FROM 

tblContact ORDER BY [tblContact].[LastName]; 



Set the number of columns to 2, set the bound column to 1 and set the 

column widths to hide the column thats displaying the member ID.



HTH



Brian



> Okay, I did it - the message box came up, I pressed "Okay", & then the 

same run-time error 

> came up (syntax problem).

> 

> +Tammy

Well, that did it to solve the run time error! However, when I select someone from the 

dropdown list now, nothing happens. The new name shows in the list box, but the record 

doesn't change. Now what?!  =)



(What does HTH mean?)



+Tammy





> Hi Tammy

> 

> Roy's point is that you seem to be passing the wrong value to your code.

> 

> You say that the combo box rowsource is:

> 

> SELECT [LastName] & ", " & [FirstName] AS Name,

> [tblContact].[LastName] FROM 

> tblContact ORDER BY [tblContact].[LastName]; 

> 

> This populates the combobox with a list of names. Your code however is 

> expecting a Member ID, which, presumably, is not the name of the member?

> 

> Private Sub MemberSearch_AfterUpdate()

>     'Find the record that matches the control.

>     Dim rs As Object

>     

>     Set rs = Me.RecordsetClone

>     rs.FindFirst "[MemberID] = " & Me![MemberSearch] & ""

>     rs.Bookmark = Me.Bookmark

> End Sub

> 

> I think you need to change a few details of the combobox to get 

everything 

> to work. Change the rowsource to:

> 

> SELECT [MemberID],[LastName] & ", " & [FirstName] AS Name,

> [tblContact].[LastName] FROM 

> tblContact ORDER BY [tblContact].[LastName]; 

> 

> Set the number of columns to 2, set the bound column to 1 and set the 

> column widths to hide the column thats displaying the member ID.

> 

> HTH

> 

> Brian

It means Hope This Helps (which, unfortunately, it didn't this time!)



As far as I can see, it should work fine now - would need to look more 

closely at your database to find what's going wrong.



-Brian



> Well, that did it to solve the run time error! However, when I select 

someone from the 

> dropdown list now, nothing happens. The new name shows in the list box, 

but the record 

> doesn't change. Now what?!  =)

> 

> (What does HTH mean?)

> 

> +Tammy

> 

> 

> > Hi Tammy

> > 

> > Roy's point is that you seem to be passing the wrong value to your 

code.

> > 

> > You say that the combo box rowsource is:

> > 

> > SELECT [LastName] & ", " & [FirstName] AS Name,

> > [tblContact].[LastName] FROM 

> > tblContact ORDER BY [tblContact].[LastName]; 

> > 

> > This populates the combobox with a list of names. Your code however is 

> > expecting a Member ID, which, presumably, is not the name of the 

member?

> > 

> > Private Sub MemberSearch_AfterUpdate()

> >     'Find the record that matches the control.

> >     Dim rs As Object

> >     

> >     Set rs = Me.RecordsetClone

> >     rs.FindFirst "[MemberID] = " & Me![MemberSearch] & ""

> >     rs.Bookmark = Me.Bookmark

> > End Sub

> > 

> > I think you need to change a few details of the combobox to get 

> everything 

> > to work. Change the rowsource to:

> > 

> > SELECT [MemberID],[LastName] & ", " & [FirstName] AS Name,

> > [tblContact].[LastName] FROM 

> > tblContact ORDER BY [tblContact].[LastName]; 

> > 

> > Set the number of columns to 2, set the bound column to 1 and set the 

> > column widths to hide the column thats displaying the member ID.

> > 

> > HTH

> > 

> > Brian