Afterupdate to populate form

[Douglas J. Steele]

Do you mean that the parameter prompt box is asking for Me.CboSchemeID?

Is that the name of the combo box on your form, or did you perhaps mistype
it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, thanks to you both, i think i'm nearly there now.
It now says enter Parameter value with the name of the selected scheme. :-(

As Steve pointed out yesterday, since you've got a space in the field name,
you need to enclose it in square brackets:

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID
End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,
I tried what u suggested and i get a Run time error 3705, Syntax error
(missing operator) in query expression 'Scheme ID = Office'


:

Steve's statement was intended to be typed on a single line, but word-wrap
makes it difficult to see that sometimes.

I've split the statement into shorter lines, with concatenation characters
are the end of each line.

The first two lines in the routine should end & _ (that's ampersand, space,
underscore),


Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"Scheme ID = " & Me.CboSchemeID
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Steve,
I copy and pasted what you wrote but access automatically added

the
extra

"
I have tried everything, drivin me nuts now. (Scheme ID is

correct
with

the
space)
thanks alot

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails"
WHERE
Scheme ID = " & Me.CboSchemeID"
End Sub


:

Angel,

This shouls all be in one line....
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

It occurs to me that your newsreader may be putting in a line

wrap
and

you have copied that to your code?

If not, please copy/paste the code from your VBE into your reply here,
let's have a look.

--
Steve Schapel, Microsoft Access MVP

Angel_1 wrote:
Hi
I really appreciate your help, but i still cant get it to work.
I get an error message saying Compile Error, Sub or funciton not
defined.
WHERE is highlighted on the VB Editor Window.

 

[Angel_1]

It Says
Enter Parameter Value
"Scheme Name" (which ever one i've chosen)
Then a blank bit here

Do you mean that the parameter prompt box is asking for Me.CboSchemeID?

Is that the name of the combo box on your form, or did you perhaps mistype
it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, thanks to you both, i think i'm nearly there now.
It now says enter Parameter value with the name of the selected scheme. :-(

As Steve pointed out yesterday, since you've got a space in the field name,
you need to enclose it in square brackets:

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID
End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,
I tried what u suggested and i get a Run time error 3705, Syntax error
(missing operator) in query expression 'Scheme ID = Office'


:

Steve's statement was intended to be typed on a single line, but
word-wrap
makes it difficult to see that sometimes.

I've split the statement into shorter lines, with concatenation
characters
are the end of each line.

The first two lines in the routine should end & _ (that's ampersand,
space,
underscore),


Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"Scheme ID = " & Me.CboSchemeID
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Steve,
I copy and pasted what you wrote but access automatically added the
extra
"
I have tried everything, drivin me nuts now. (Scheme ID is correct
with
the
space)
thanks alot

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails"
WHERE
Scheme ID = " & Me.CboSchemeID"
End Sub


:

Angel,

This shouls all be in one line....
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

It occurs to me that your newsreader may be putting in a line wrap
and
you have copied that to your code?

If not, please copy/paste the code from your VBE into your reply
here,
let's have a look.

--
Steve Schapel, Microsoft Access MVP

Angel_1 wrote:
Hi
I really appreciate your help, but i still cant get it to work.
I get an error message saying Compile Error, Sub or funciton not
defined.
WHERE is highlighted on the VB Editor Window.

 

[Douglas J. Steele]

Ah. Scheme ID is text, then, not numeric.

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = '" & Me.CboSchemeID & "'"
End Sub

Exagerated for clarity, that last line is:

"[Scheme ID] = ' " & Me.CboSchemeID & " ' "

If there's a chance that there might be an apostrophe in the scheme, try

"[Scheme ID] = " & Chr$(34) & Me.CboSchemeID & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It Says
Enter Parameter Value
"Scheme Name" (which ever one i've chosen)
Then a blank bit here

Do you mean that the parameter prompt box is asking for Me.CboSchemeID?

Is that the name of the combo box on your form, or did you perhaps mistype
it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, thanks to you both, i think i'm nearly there now.
It now says enter Parameter value with the name of the selected

scheme.
:-(

:

As Steve pointed out yesterday, since you've got a space in the

field
name,

you need to enclose it in square brackets:

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID
End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,
I tried what u suggested and i get a Run time error 3705, Syntax error
(missing operator) in query expression 'Scheme ID = Office'


:

Steve's statement was intended to be typed on a single line, but
word-wrap
makes it difficult to see that sometimes.

I've split the statement into shorter lines, with concatenation
characters
are the end of each line.

The first two lines in the routine should end & _ (that's ampersand,
space,
underscore),


Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"Scheme ID = " & Me.CboSchemeID
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Steve,
I copy and pasted what you wrote but access automatically

added
the

extra
"
I have tried everything, drivin me nuts now. (Scheme ID is correct
with
the
space)
thanks alot

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails"
WHERE
Scheme ID = " & Me.CboSchemeID"
End Sub


:

Angel,

This shouls all be in one line....
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

It occurs to me that your newsreader may be putting in a

line
wrap

and
you have copied that to your code?

If not, please copy/paste the code from your VBE into your reply
here,
let's have a look.

--
Steve Schapel, Microsoft Access MVP

Angel_1 wrote:
Hi
I really appreciate your help, but i still cant get it to work.
I get an error message saying Compile Error, Sub or

funciton
not

defined.
WHERE is highlighted on the VB Editor Window.

 

[Angel_1]

THANKYOU, IT WORKS!!!!!
Haha, ANy chance you can tell me how i start off showing no detials on the
form?
Thanks again

Ah. Scheme ID is text, then, not numeric.

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = '" & Me.CboSchemeID & "'"
End Sub

Exagerated for clarity, that last line is:

"[Scheme ID] = ' " & Me.CboSchemeID & " ' "

If there's a chance that there might be an apostrophe in the scheme, try

"[Scheme ID] = " & Chr$(34) & Me.CboSchemeID & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It Says
Enter Parameter Value
"Scheme Name" (which ever one i've chosen)
Then a blank bit here

Do you mean that the parameter prompt box is asking for Me.CboSchemeID?

Is that the name of the combo box on your form, or did you perhaps mistype
it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, thanks to you both, i think i'm nearly there now.
It now says enter Parameter value with the name of the selected scheme.
:-(


:

As Steve pointed out yesterday, since you've got a space in the field
name,
you need to enclose it in square brackets:

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"[Scheme ID] = " & Me.CboSchemeID
End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas,
I tried what u suggested and i get a Run time error 3705, Syntax error
(missing operator) in query expression 'Scheme ID = Office'


:

Steve's statement was intended to be typed on a single line, but
word-wrap
makes it difficult to see that sometimes.

I've split the statement into shorter lines, with concatenation
characters
are the end of each line.

The first two lines in the routine should end & _ (that's ampersand,
space,
underscore),


Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM " & _
"QryCompanyFormDetails WHERE " & _
"Scheme ID = " & Me.CboSchemeID
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Steve,
I copy and pasted what you wrote but access automatically added
the
extra
"
I have tried everything, drivin me nuts now. (Scheme ID is
correct
with
the
space)
thanks alot

Private Sub CboSchemeID_AfterUpdate()
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails"
WHERE
Scheme ID = " & Me.CboSchemeID"
End Sub


:

Angel,

This shouls all be in one line....
Me.RecordSource = "SELECT * FROM QryCompanyFormDetails WHERE
SchemeID=" & Me.cboSchemeID

It occurs to me that your newsreader may be putting in a line
wrap
and
you have copied that to your code?

If not, please copy/paste the code from your VBE into your reply
here,
let's have a look.

--
Steve Schapel, Microsoft Access MVP

Angel_1 wrote:
Hi
I really appreciate your help, but i still cant get it to
work.
I get an error message saying Compile Error, Sub or funciton
not
defined.
WHERE is highlighted on the VB Editor Window.

 

[Steve Schapel]

Hi Angel. Me again

One way, if you mean absolutely nothing showing on the form, would be a
variation on the theme we have been discussing. In design view of the
form, set the Record Source property to something like this...
SELECT * FROM QryCompanyFormDetails WHERE [Scheme ID]="xxx"
(where xxx is something that will never be used as a real Scheme ID).

 

[Angel_1]

Hi, i bet u thought u'd got rid of me. That worked, thanks.
Only problem i'm having now is when i select two of the schemes in the combo
box i get an error
Run-time error'3705'
Syntax error (missing operator) in query expression '[Scheme ID] = 'scheme
name".

Any ideas please? all the others work fine apart from two.
Thanks

Hi Angel. Me again

One way, if you mean absolutely nothing showing on the form, would be a
variation on the theme we have been discussing. In design view of the
form, set the Record Source property to something like this...
SELECT * FROM QryCompanyFormDetails WHERE [Scheme ID]="xxx"
(where xxx is something that will never be used as a real Scheme ID).

--
Steve Schapel, Microsoft Access MVP

THANKYOU, IT WORKS!!!!!
Haha, ANy chance you can tell me how i start off showing no detials on the
form?
Thanks again

 

[Steve Schapel]

Angel,

Don't suppose these two scheme names have an apostrophe in them, do
they? If not, please give us a clue what they are, and also some
examples of others that do work. If it is an apostrophe in the name of
the scheme that is causing the problem, change the code to like this...
...WHERE [Scheme ID] = " & """" & Me.CboSchemeID & """"