Data from tables

[Guest]

Okay. Think I wasn't clear.

Have a table called FDA that contains tons of data.
Have table called Log
Have table called Daily totals
Have a query called Foods_qry

The source for the form is the query. The source for the field called Food
is the table FDA. This is what I did based on what you said...

With Me.RecordsetClone
.FindFirst "[Calories] = " & Me.Food
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If

I get an error: Run Time Error '3077' Syntax error (comma) in expression.
It points to .FindFirst "[Calories] = " & Me.Food

Obviously I'm doing something wrong...
Help please.
Thanks

 

[John W. Vinson]

The source for the form is the query. The source for the field called Food
is the table FDA. This is what I did based on what you said...

With Me.RecordsetClone
.FindFirst "[Calories] = " & Me.Food
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If

I get an error: Run Time Error '3077' Syntax error (comma) in expression.
It points to .FindFirst "[Calories] = " & Me.Food

Obviously I'm doing something wrong...
Help please.

What's the datatype of Calories? What's in the control named Food? Does the
control contain a comma?

John W. Vinson [MVP]

 

[UpRider]

Stephanie,
.FindFirst "[Calories] = " & Me.Food
is doing this:
Display on the form the first record found in Foods_qry where the column
'Calories' in that query matches the value I have in the combobox 'Food' on
my form.

This doesn't seem like a likely match, the value in 'Calories' matching the
value in 'Food'.

In any case, when you get the names correct, if the value in your combobox
is not a number (and it probabyl is not), you have to do it like this:
.FindFirst "[Calories] = " & chr$(39) & Me.Food & chr$(39)

Hope this works for you, UpRider

 

[Guest]

Data type for Calories is Number
--
Steph

The source for the form is the query. The source for the field called Food
is the table FDA. This is what I did based on what you said...

With Me.RecordsetClone
.FindFirst "[Calories] = " & Me.Food
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If

I get an error: Run Time Error '3077' Syntax error (comma) in expression.
It points to .FindFirst "[Calories] = " & Me.Food

Obviously I'm doing something wrong...
Help please.

What's the datatype of Calories? What's in the control named Food? Does the
control contain a comma?

John W. Vinson [MVP]

 

[Guest]

Okay, I changed it to read:
With Me.RecordsetClone
.FindFirst "Food= " & Chr$(39) & Me.Food & Chr$(39)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Then got the error: "Update or CancelUpdate without AddNew or Edit"

--
Steph

Stephanie,
.FindFirst "[Calories] = " & Me.Food
is doing this:
Display on the form the first record found in Foods_qry where the column
'Calories' in that query matches the value I have in the combobox 'Food' on
my form.

This doesn't seem like a likely match, the value in 'Calories' matching the
value in 'Food'.

In any case, when you get the names correct, if the value in your combobox
is not a number (and it probabyl is not), you have to do it like this:
.FindFirst "[Calories] = " & chr$(39) & Me.Food & chr$(39)

Hope this works for you, UpRider

Okay. Think I wasn't clear.

Have a table called FDA that contains tons of data.
Have table called Log
Have table called Daily totals
Have a query called Foods_qry

The source for the form is the query. The source for the field called
Food
is the table FDA. This is what I did based on what you said...

With Me.RecordsetClone
.FindFirst "[Calories] = " & Me.Food
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If

I get an error: Run Time Error '3077' Syntax error (comma) in expression.
It points to .FindFirst "[Calories] = " & Me.Food

Obviously I'm doing something wrong...
Help please.
Thanks

 

[John W. Vinson]

Data type for Calories is Number

Ok; how about the other questions?

What's in the control named Food? Does the control contain a comma?


John W. Vinson [MVP]

 

[Guest]

What's in the control named Food?
Food

That info comes from a table named FDA and the info in my combo box comes
from the field Food.

Does the control contain a comma?
No. No commas.

 

[John W. Vinson]

What's in the control named Food?
Food

That info comes from a table named FDA and the info in my combo box comes
from the field Food.

Does the control contain a comma?
No. No commas.

Stephanie, it's like pulling teeth to get any information from you. I'm an
unpaid volunteer like everyone else here; I'm trying to help; but you're
making it really difficult. I'll keep trying for a couple more posts...

Why are you searching for records where the field named Calories is equal to
the value in the control named Food? What are some REAL LIFE EXAMPLES from
your table of the contents of the field named Calories, and the field named
Food?

John W. Vinson [MVP]

 

[Guest]

Okay, guess I am still not clear. Here's the scenario. I have a table that
has foods and all their nutritional values. The plan here is to track what
I'm eating. In the form, the combo box lists the foods. When I click on
Pizza I want the calories and fat grams, etc to show up in the text boxes I
have in the form for calories and fat grams etc.

More info...The source for the form is a query. The FDA table is only to
provide the data

I know I've done something similar for a class, but I'm trying to avoid
reinstalling VB.NET to access my assignments from that course.

 

[John W. Vinson]

Okay, guess I am still not clear. Here's the scenario. I have a table that
has foods and all their nutritional values. The plan here is to track what
I'm eating. In the form, the combo box lists the foods. When I click on
Pizza I want the calories and fat grams, etc to show up in the text boxes I
have in the form for calories and fat grams etc.

What is the RowSource property of the combo box? I presume it's a query on the
Foods table; please open the rowsource in query design view, go to View...
SQL, and post the SQL text here.

You need to include the calories, fat, etc. within the query that feeds the
combo box (which I'll call cboFood). For instance, if the combo's row source
were

SELECT FoodID, FoodName, Calories, Fat, Carbohydrate
FROM Foods
ORDER BY FoodName;

then you could put a Textbox on the form with a Control Source

=cboFood.Column(2)

to display the third column (it's zero based, FoodID is 0, FoodName is 1,
etc.) from the combo box.

More info...The source for the form is a query. The FDA table is only to
provide the data

Again: YOU CAN SEE YOUR DATABASE.
I CANNOT SEE YOUR DATABASE.

You know (or can find out) what fields exist in the FDA table.
You know (or can find out) what fields are included in the query.

Please reread your posts with the mindset that you are talking to someone who
may be an expert in Access, but who is not a mindreader and who does not have
any way to find out anything about your database *other than what you type*.

I know I've done something similar for a class, but I'm trying to avoid
reinstalling VB.NET to access my assignments from that course.

It will not be necessary (or probably even helpful) to install VB.Net.

John W. Vinson [MVP]

 

[Guest]

The row source is the table FDA which holds all the data I want to bring into
the form. The field Food is from that table.

 

[Guest]

Okay. I just changed it after making a query called FDA_qry, which is now
the row source for the field Food.

 

[John W. Vinson]

Okay. I just changed it after making a query called FDA_qry, which is now
the row source for the field Food.

I'll be glad to help you if you'll answer my questions. Otherwise I cannot.

John W. Vinson [MVP]

 

[Guest]

Your last post asked the row source was....it was based on a table, but in
your response you assumed it was based on a query. So I made a query and
made that my row source. I did answer your question.

I can really do without the sarcasm and nastiness, so I'll look elsewhere
for help. This I find really too bad. I usually get god help using these
newsgroups from very nice individuals.

 

[UpRider]

Well, John, you're the current poster boy for "Let no good deed go
unpunished".
All that effort down the tubes with an insult for your reward.
Be assured the grateful far outnumber the ungrateful.

UpRider

 

[John W. Vinson]

Your last post asked the row source was....it was based on a table, but in
your response you assumed it was based on a query. So I made a query and
made that my row source. I did answer your question.

I'm sorry, Stephanie. My frustration got the better of my good judgement.

I did ask - repeatedly - that you post the SQL view of the query. I suspect
that the error could be found in that view, and your posting that you used "a
query" does nothing to help find that error.

I suggest that you post a new thread, just because some of the experts here
might not be following this one. Or you might want to talk to the good folks
at http://www.utteraccess.com.


John W. Vinson [MVP]

 

[Gina Whipp]

Stephaie,

If calories is umeric and food is text then that might be the reason you are
getting this error.

"[iItemID] = """ & Me![cboItemID] & """" <--for text OR "[iInvoiceID] =
'" & Me![cboInvoiceID] & "'"

"mrMaterialID = " & Me![cboMaterialID] <-- for numeric

So if calories is numeric and food is text then that might be your problem.
If ot in your EXT post PLEASE post the either the query SQL or the tblFood
field list ad the field list for where the calories are comig from.

And perhaps you want to go BACK OVER your posts because try as John might,
he could not get a straight answer out of you. Remember you can see your
database no one here can. We need EVERY piece of information we ask for to
be able to assist.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[Gina Whipp]

Okay let's try something else because what you typed in was not what was
suggested... Copy and paste this in the AfterUpdate event of the combo box.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst .FindFirst "[Calories] = " & chr$(39) & Me.Food & chr$(39)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Okay, I changed it to read:
With Me.RecordsetClone
.FindFirst "Food= " & Chr$(39) & Me.Food & Chr$(39)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Then got the error: "Update or CancelUpdate without AddNew or Edit"

--
Steph

Stephanie,
.FindFirst "[Calories] = " & Me.Food
is doing this:
Display on the form the first record found in Foods_qry where the column
'Calories' in that query matches the value I have in the combobox 'Food'
on
my form.

This doesn't seem like a likely match, the value in 'Calories' matching
the
value in 'Food'.

In any case, when you get the names correct, if the value in your
combobox
is not a number (and it probabyl is not), you have to do it like this:
.FindFirst "[Calories] = " & chr$(39) & Me.Food & chr$(39)

Hope this works for you, UpRider

Okay. Think I wasn't clear.

Have a table called FDA that contains tons of data.
Have table called Log
Have table called Daily totals
Have a query called Foods_qry

The source for the form is the query. The source for the field called
Food
is the table FDA. This is what I did based on what you said...

With Me.RecordsetClone
.FindFirst "[Calories] = " & Me.Food
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If

I get an error: Run Time Error '3077' Syntax error (comma) in
expression.
It points to .FindFirst "[Calories] = " & Me.Food

Obviously I'm doing something wrong...
Help please.
Thanks