Using Query instead of Table

[XXAA]

I have a form that is filled in by data from a table and is saved to a
second table. This for creating an invoice from customer table, the reason
that I do this is so that the invoice has the correct details at time of
creation. If the customer address changes the previously created invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] = [CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of a Table

I have tried using QueryDef and QueryDefs but I have not got anywhere.

Any help would be appreciated.



Regards,


John A

 

[Dan Artuso]

Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

 

[XXAA]

Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is a saved
query)

now I get errors here (Operation is not supported for this type of Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list display all
the customers, select say the 5th one down and the data entered is the 1st
record. It does not matter what you select it only enters the first on the
combo list.


Any help would be appreciated,


John A

Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP

I have a form that is filled in by data from a table and is saved to a
second table. This for creating an invoice from customer table, the reason
that I do this is so that the invoice has the correct details at time of
creation. If the customer address changes the previously created invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] = [CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of a Table

I have tried using QueryDef and QueryDefs but I have not got anywhere.

Any help would be appreciated.



Regards,


John A

 

[Dan Artuso]

Hi,
Yes, I should have mentioned that Seek only works with 'table' type recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP

Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is a saved
query)

now I get errors here (Operation is not supported for this type of Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list display all
the customers, select say the 5th one down and the data entered is the 1st
record. It does not matter what you select it only enters the first on the
combo list.


Any help would be appreciated,


John A

Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP

I have a form that is filled in by data from a table and is saved to a
second table. This for creating an invoice from customer table, the reason
that I do this is so that the invoice has the correct details at time of
creation. If the customer address changes the previously created invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] = [CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of a Table

I have tried using QueryDef and QueryDefs but I have not got anywhere.

Any help would be appreciated.



Regards,


John A

 

[XXAA]

Dan,

Thanks for your replies, I have had a look at the help for both FindFirst
and Seek (Access2003) and to me they don't help me a lot, they appear to be
written for users who know the answer.


Regards,


John A

Hi,
Yes, I should have mentioned that Seek only works with 'table' type recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP

Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is a saved
query)

now I get errors here (Operation is not supported for this type of Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list display all
the customers, select say the 5th one down and the data entered is the 1st
record. It does not matter what you select it only enters the first on the
combo list.


Any help would be appreciated,


John A

Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is saved to a
second table. This for creating an invoice from customer table, the reason
that I do this is so that the invoice has the correct details at time of
creation. If the customer address changes the previously created invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] = [CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of a Table

I have tried using QueryDef and QueryDefs but I have not got anywhere.

Any help would be appreciated.



Regards,


John A

 

[Dan Artuso]

Hi,
Is it the syntax that is confusing?

Let's say you want to find a match on a field called CustomerId
and the control that contains the value is called txtCustId

CustomerList.FindFirst "CustomerId = " & txtCustId

Now, if the criteria were a string value then you have to delimit it with quotes:
CustomerList.FindFirst "CustomerId = '" & txtCustId & "'"

Does that make sense to you?

--
HTH
Dan Artuso, Access MVP

Dan,

Thanks for your replies, I have had a look at the help for both FindFirst
and Seek (Access2003) and to me they don't help me a lot, they appear to be
written for users who know the answer.


Regards,


John A

Hi,
Yes, I should have mentioned that Seek only works with 'table' type recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP

Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is a saved
query)

now I get errors here (Operation is not supported for this type of Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list display all
the customers, select say the 5th one down and the data entered is the 1st
record. It does not matter what you select it only enters the first on the
combo list.


Any help would be appreciated,


John A




Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is saved to a
second table. This for creating an invoice from customer table, the
reason
that I do this is so that the invoice has the correct details at time of
creation. If the customer address changes the previously created invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] = [CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of a Table

I have tried using QueryDef and QueryDefs but I have not got anywhere.

Any help would be appreciated.



Regards,


John A

 

[XXAA]

Dan,




This is what I have entered and it is giving me the following error





Runtime Error 3077



Syntax Error (missing operator) in expression





The CustomerID is from qryCustomer and is an AutoNumber



docCustomerID in on the Form that I want filled in and is a number (Long
Integer)









Private Sub cboCustomerLookup_AfterUpdate()



Dim MyDB As Database

Dim CustomerList As Recordset

Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("qryCustomer")



CustomerList.FindFirst "CustomerID = " & Me.docCustomerID




Regards,


John A

Hi,
Is it the syntax that is confusing?

Let's say you want to find a match on a field called CustomerId
and the control that contains the value is called txtCustId

CustomerList.FindFirst "CustomerId = " & txtCustId

Now, if the criteria were a string value then you have to delimit it with quotes:
CustomerList.FindFirst "CustomerId = '" & txtCustId & "'"

Does that make sense to you?

--
HTH
Dan Artuso, Access MVP

Dan,

Thanks for your replies, I have had a look at the help for both FindFirst
and Seek (Access2003) and to me they don't help me a lot, they appear to be
written for users who know the answer.


Regards,


John A

Hi,
Yes, I should have mentioned that Seek only works with 'table' type recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP


Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is a saved
query)

now I get errors here (Operation is not supported for this type of Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list

display
all

the customers, select say the 5th one down and the data entered is

the
1st

record. It does not matter what you select it only enters the first

on
the

combo list.


Any help would be appreciated,


John A




Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is

saved to
a

second table. This for creating an invoice from customer table, the
reason
that I do this is so that the invoice has the correct details at time of
creation. If the customer address changes the previously created invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] = [CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of

a
Table

I have tried using QueryDef and QueryDefs but I have not got anywhere.

Any help would be appreciated.



Regards,


John A

 

[Dan Artuso]

Hi,
I don't see anything wrong with your syntax.
Are you sure that Me.docCustomerID has a value?
Let's try this. Create a string variable: strWhere

Dim strWhere As String




Alter your code like this:
strWhere = "CustomerID = " & Me.docCustomerID
MsgBox strWhere
CustomerList.FindFirst strWhere

What does strWhere look like?

Oh, I'm assuming that when the code errors out, your FindFirst line is
the line causing he error??

--
HTH
Dan Artuso, Access MVP

Dan,




This is what I have entered and it is giving me the following error





Runtime Error 3077



Syntax Error (missing operator) in expression





The CustomerID is from qryCustomer and is an AutoNumber



docCustomerID in on the Form that I want filled in and is a number (Long
Integer)









Private Sub cboCustomerLookup_AfterUpdate()



Dim MyDB As Database

Dim CustomerList As Recordset

Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("qryCustomer")



CustomerList.FindFirst "CustomerID = " & Me.docCustomerID




Regards,


John A

Hi,
Is it the syntax that is confusing?

Let's say you want to find a match on a field called CustomerId
and the control that contains the value is called txtCustId

CustomerList.FindFirst "CustomerId = " & txtCustId

Now, if the criteria were a string value then you have to delimit it with quotes:
CustomerList.FindFirst "CustomerId = '" & txtCustId & "'"

Does that make sense to you?

--
HTH
Dan Artuso, Access MVP

Dan,

Thanks for your replies, I have had a look at the help for both FindFirst
and Seek (Access2003) and to me they don't help me a lot, they appear to be
written for users who know the answer.


Regards,


John A






Hi,
Yes, I should have mentioned that Seek only works with 'table' type
recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP


Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is a saved
query)

now I get errors here (Operation is not supported for this type of
Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list display
all
the customers, select say the 5th one down and the data entered is the
1st
record. It does not matter what you select it only enters the first on
the
combo list.


Any help would be appreciated,


John A




Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is saved to
a
second table. This for creating an invoice from customer table, the
reason
that I do this is so that the invoice has the correct details at
time of
creation. If the customer address changes the previously created
invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] =
[CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of a
Table

I have tried using QueryDef and QueryDefs but I have not got
anywhere.

Any help would be appreciated.



Regards,


John A

 

[XXAA]

Dan,

It shows CustomerID =

I think that I have not explained fully what I am trying to do.

I have a Form (single form) and its record source is a table called
tblDocuments.

On that form I have a combo box that looks up records from a query called
qryCustomers when I select a record I want its information to fill in the
form.

e.g. CustomerID (from qryCustomers) to be copied to docCustomerID (on the
Form)

and all the other fields that are listed in the code.(same ones as in my
original code)

The query that I want to use is based on the table that I used in the
original code.

The first code that I posted works fine, I just want to change the source
from a table to a query.


Thank you for your continued help,

Regards,


John A

Hi,
I don't see anything wrong with your syntax.
Are you sure that Me.docCustomerID has a value?
Let's try this. Create a string variable: strWhere

Dim strWhere As String




Alter your code like this:
strWhere = "CustomerID = " & Me.docCustomerID
MsgBox strWhere
CustomerList.FindFirst strWhere

What does strWhere look like?

Oh, I'm assuming that when the code errors out, your FindFirst line is
the line causing he error??

--
HTH
Dan Artuso, Access MVP

Dan,




This is what I have entered and it is giving me the following error





Runtime Error 3077



Syntax Error (missing operator) in expression





The CustomerID is from qryCustomer and is an AutoNumber



docCustomerID in on the Form that I want filled in and is a number (Long
Integer)









Private Sub cboCustomerLookup_AfterUpdate()



Dim MyDB As Database

Dim CustomerList As Recordset

Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("qryCustomer")



CustomerList.FindFirst "CustomerID = " & Me.docCustomerID




Regards,


John A

Hi,
Is it the syntax that is confusing?

Let's say you want to find a match on a field called CustomerId
and the control that contains the value is called txtCustId

CustomerList.FindFirst "CustomerId = " & txtCustId

Now, if the criteria were a string value then you have to delimit it

with
quotes:

CustomerList.FindFirst "CustomerId = '" & txtCustId & "'"

Does that make sense to you?

--
HTH
Dan Artuso, Access MVP


Dan,

Thanks for your replies, I have had a look at the help for both FindFirst
and Seek (Access2003) and to me they don't help me a lot, they

appear to
be

written for users who know the answer.


Regards,


John A






Hi,
Yes, I should have mentioned that Seek only works with 'table' type
recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP


Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is

a
saved

query)

now I get errors here (Operation is not supported for this type of
Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list display
all
the customers, select say the 5th one down and the data entered

is
the

1st
record. It does not matter what you select it only enters the

first
on

the
combo list.


Any help would be appreciated,


John A




Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is saved to
a
second table. This for creating an invoice from customer

table,
the

reason
that I do this is so that the invoice has the correct details at
time of
creation. If the customer address changes the previously created
invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] =
[CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu

of
a

Table

I have tried using QueryDef and QueryDefs but I have not got
anywhere.

Any help would be appreciated.



Regards,


John A

 

[Dan Artuso]

Hi,
Yes, but in your original code you use Screen.ActiveControl
What control is that?
That should be the one you use as criteria.


--
HTH
Dan Artuso, Access MVP

Dan,

It shows CustomerID =

I think that I have not explained fully what I am trying to do.

I have a Form (single form) and its record source is a table called
tblDocuments.

On that form I have a combo box that looks up records from a query called
qryCustomers when I select a record I want its information to fill in the
form.

e.g. CustomerID (from qryCustomers) to be copied to docCustomerID (on the
Form)

and all the other fields that are listed in the code.(same ones as in my
original code)

The query that I want to use is based on the table that I used in the
original code.

The first code that I posted works fine, I just want to change the source
from a table to a query.


Thank you for your continued help,

Regards,


John A

Hi,
I don't see anything wrong with your syntax.
Are you sure that Me.docCustomerID has a value?
Let's try this. Create a string variable: strWhere

Dim strWhere As String




Alter your code like this:
strWhere = "CustomerID = " & Me.docCustomerID
MsgBox strWhere
CustomerList.FindFirst strWhere

What does strWhere look like?

Oh, I'm assuming that when the code errors out, your FindFirst line is
the line causing he error??

--
HTH
Dan Artuso, Access MVP

Dan,




This is what I have entered and it is giving me the following error





Runtime Error 3077



Syntax Error (missing operator) in expression





The CustomerID is from qryCustomer and is an AutoNumber



docCustomerID in on the Form that I want filled in and is a number (Long
Integer)









Private Sub cboCustomerLookup_AfterUpdate()



Dim MyDB As Database

Dim CustomerList As Recordset

Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("qryCustomer")



CustomerList.FindFirst "CustomerID = " & Me.docCustomerID




Regards,


John A





Hi,
Is it the syntax that is confusing?

Let's say you want to find a match on a field called CustomerId
and the control that contains the value is called txtCustId

CustomerList.FindFirst "CustomerId = " & txtCustId

Now, if the criteria were a string value then you have to delimit it with
quotes:
CustomerList.FindFirst "CustomerId = '" & txtCustId & "'"

Does that make sense to you?

--
HTH
Dan Artuso, Access MVP


Dan,

Thanks for your replies, I have had a look at the help for both
FindFirst
and Seek (Access2003) and to me they don't help me a lot, they appear to
be
written for users who know the answer.


Regards,


John A






Hi,
Yes, I should have mentioned that Seek only works with 'table' type
recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP


Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is a
saved
query)

now I get errors here (Operation is not supported for this type of
Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list
display
all
the customers, select say the 5th one down and the data entered is
the
1st
record. It does not matter what you select it only enters the first
on
the
combo list.


Any help would be appreciated,


John A




Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is
saved to
a
second table. This for creating an invoice from customer table,
the
reason
that I do this is so that the invoice has the correct details at
time of
creation. If the customer address changes the previously created
invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] =
[CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu of
a
Table

I have tried using QueryDef and QueryDefs but I have not got
anywhere.

Any help would be appreciated.



Regards,


John A

 

[XXAA]

Dan,

If I "rem" out the line CustomerList.Seek "=", Screen.ActiveControl , the
current one I am using will not work, it only uses the first record in list
and not the selected one.

I have read the help on Active Control and it makes sense to me.


I don't understand what you mean by "That should be the one you use as
criteria."


John A

Hi,
Yes, but in your original code you use Screen.ActiveControl
What control is that?
That should be the one you use as criteria.


--
HTH
Dan Artuso, Access MVP

Dan,

It shows CustomerID =

I think that I have not explained fully what I am trying to do.

I have a Form (single form) and its record source is a table called
tblDocuments.

On that form I have a combo box that looks up records from a query called
qryCustomers when I select a record I want its information to fill in the
form.

e.g. CustomerID (from qryCustomers) to be copied to docCustomerID (on the
Form)

and all the other fields that are listed in the code.(same ones as in my
original code)

The query that I want to use is based on the table that I used in the
original code.

The first code that I posted works fine, I just want to change the source
from a table to a query.


Thank you for your continued help,

Regards,


John A

Hi,
I don't see anything wrong with your syntax.
Are you sure that Me.docCustomerID has a value?
Let's try this. Create a string variable: strWhere

Dim strWhere As String




Alter your code like this:
strWhere = "CustomerID = " & Me.docCustomerID
MsgBox strWhere
CustomerList.FindFirst strWhere

What does strWhere look like?

Oh, I'm assuming that when the code errors out, your FindFirst line is
the line causing he error??

--
HTH
Dan Artuso, Access MVP


Dan,




This is what I have entered and it is giving me the following error





Runtime Error 3077



Syntax Error (missing operator) in expression





The CustomerID is from qryCustomer and is an AutoNumber



docCustomerID in on the Form that I want filled in and is a number (Long
Integer)









Private Sub cboCustomerLookup_AfterUpdate()



Dim MyDB As Database

Dim CustomerList As Recordset

Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("qryCustomer")



CustomerList.FindFirst "CustomerID = " & Me.docCustomerID




Regards,


John A





Hi,
Is it the syntax that is confusing?

Let's say you want to find a match on a field called CustomerId
and the control that contains the value is called txtCustId

CustomerList.FindFirst "CustomerId = " & txtCustId

Now, if the criteria were a string value then you have to delimit

it
with

quotes:
CustomerList.FindFirst "CustomerId = '" & txtCustId & "'"

Does that make sense to you?

--
HTH
Dan Artuso, Access MVP


Dan,

Thanks for your replies, I have had a look at the help for both
FindFirst
and Seek (Access2003) and to me they don't help me a lot, they appear to
be
written for users who know the answer.


Regards,


John A






Hi,
Yes, I should have mentioned that Seek only works with 'table' type
recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP


Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which

is
a

saved
query)

now I get errors here (Operation is not supported for this

type
of

Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list
display
all
the customers, select say the 5th one down and the data

entered
is

the
1st
record. It does not matter what you select it only enters

the
first

on
the
combo list.


Any help would be appreciated,


John A




Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is
saved to
a
second table. This for creating an invoice from customer table,
the
reason
that I do this is so that the invoice has the correct details at
time of
creation. If the customer address changes the previously created
invoice
holds the old address which was current at the time of creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] =
[CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and

lieu
of

a
Table

I have tried using QueryDef and QueryDefs but I have not got
anywhere.

Any help would be appreciated.



Regards,


John A

 

[XXAA]

Dan,

I got it to work!!!!!!


Dim MyDB As Database
Dim CustomerList As Recordset
Dim stLinkCriteria As String


Set MyDB = CurrentDb()
Set CustomerList = MyDB.OpenRecordset("qryCustomer2")

stLinkCriteria = Me.cboCustomerLookup

strWhere = "CustomerID = " & stLinkCriteria
'MsgBox strWhere (I "rem" this out)
CustomerList.FindFirst strWhere



Me![docCustomerID] = [CustomerList]![CustomerID]

Me![docTitle] = [CustomerList]![cusTitle]
Me![docFirstName] = [CustomerList]![cusFirstName]
Me![docSurname] = [CustomerList]![cusSurname]
Me![docCompany] = [CustomerList]![cusCompany]
Me![docStreet] = [CustomerList]![cusStreet]
Me![docCity] = [CustomerList]![cusCity]
Me![docPhoneNumber] = [CustomerList]![cusPhoneNumber]
Me![docMobileNumber] = [CustomerList]![cusMobileNumber]
Me![docFacsimileNumber] = [CustomerList]![cusFacsimileNumber]
Me![docEMail] = [CustomerList]![cusEMail]
Me![docSalutation] = [CustomerList]![cusSalutation]



Thank you for making me do it myself.

Regards,


John A

Dan,

If I "rem" out the line CustomerList.Seek "=", Screen.ActiveControl , the
current one I am using will not work, it only uses the first record in list
and not the selected one.

I have read the help on Active Control and it makes sense to me.


I don't understand what you mean by "That should be the one you use as
criteria."


John A

Hi,
Yes, but in your original code you use Screen.ActiveControl
What control is that?
That should be the one you use as criteria.

delimit

it

(which

is

and

is

saved to
a
second table. This for creating an invoice from customer
table,
the
reason
that I do this is so that the invoice has the correct
details at
time of
creation. If the customer address changes the previously
created
invoice
holds the old address which was current at the time of
creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] =
[CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source

and

lieu

not

got

 

[Dan Artuso]

Hi,
What I meant was:
Whatever control that is on your form that holds the Customer Id,
that is the control you should be using in your FindFirst statement, but I see you've
figured that out.

I had assumed that you understood the orginal code you posted and therefore
were at a certain level of coding, my mistake.

--
HTH
Dan Artuso, Access MVP

Dan,

If I "rem" out the line CustomerList.Seek "=", Screen.ActiveControl , the
current one I am using will not work, it only uses the first record in list
and not the selected one.

I have read the help on Active Control and it makes sense to me.


I don't understand what you mean by "That should be the one you use as
criteria."


John A

Hi,
Yes, but in your original code you use Screen.ActiveControl
What control is that?
That should be the one you use as criteria.


--
HTH
Dan Artuso, Access MVP

Dan,

It shows CustomerID =

I think that I have not explained fully what I am trying to do.

I have a Form (single form) and its record source is a table called
tblDocuments.

On that form I have a combo box that looks up records from a query called
qryCustomers when I select a record I want its information to fill in the
form.

e.g. CustomerID (from qryCustomers) to be copied to docCustomerID (on the
Form)

and all the other fields that are listed in the code.(same ones as in my
original code)

The query that I want to use is based on the table that I used in the
original code.

The first code that I posted works fine, I just want to change the source
from a table to a query.


Thank you for your continued help,

Regards,


John A






Hi,
I don't see anything wrong with your syntax.
Are you sure that Me.docCustomerID has a value?
Let's try this. Create a string variable: strWhere

Dim strWhere As String




Alter your code like this:
strWhere = "CustomerID = " & Me.docCustomerID
MsgBox strWhere
CustomerList.FindFirst strWhere

What does strWhere look like?

Oh, I'm assuming that when the code errors out, your FindFirst line is
the line causing he error??

--
HTH
Dan Artuso, Access MVP


Dan,




This is what I have entered and it is giving me the following error





Runtime Error 3077



Syntax Error (missing operator) in expression





The CustomerID is from qryCustomer and is an AutoNumber



docCustomerID in on the Form that I want filled in and is a number (Long
Integer)









Private Sub cboCustomerLookup_AfterUpdate()



Dim MyDB As Database

Dim CustomerList As Recordset

Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("qryCustomer")



CustomerList.FindFirst "CustomerID = " & Me.docCustomerID




Regards,


John A





Hi,
Is it the syntax that is confusing?

Let's say you want to find a match on a field called CustomerId
and the control that contains the value is called txtCustId

CustomerList.FindFirst "CustomerId = " & txtCustId

Now, if the criteria were a string value then you have to delimit it
with
quotes:
CustomerList.FindFirst "CustomerId = '" & txtCustId & "'"

Does that make sense to you?

--
HTH
Dan Artuso, Access MVP


Dan,

Thanks for your replies, I have had a look at the help for both
FindFirst
and Seek (Access2003) and to me they don't help me a lot, they
appear to
be
written for users who know the answer.


Regards,


John A






Hi,
Yes, I should have mentioned that Seek only works with 'table'
type
recordsets
Use one the Find methods instead (FindFirst perhaps).

Read up on the Seek and FindFirst methods in Help.

--
HTH
Dan Artuso, Access MVP


Dan,

Thank you for your reply, I changed this

Set CustomerList = MyDB.OpenRecordset("Customer")

to
Set CustomerList = MyDB.OpenRecordset("qryCustomer") (which is
a
saved
query)

now I get errors here (Operation is not supported for this type
of
Object)

CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl

If CustomerList.NoMatch Then

If I "rem" them out it works but not correctly. The combo list
display
all
the customers, select say the 5th one down and the data entered
is
the
1st
record. It does not matter what you select it only enters the
first
on
the
combo list.


Any help would be appreciated,


John A




Hi,
You can create a query and save it and then use it thus:
Set CustomerList = MyDB.OpenRecordset("qryYouSaved")

Or you can actually write the Select statement yourself

strSql = "Select * From Customer Order By someField"
Set CustomerList = MyDB.OpenRecordset(strSql)

--
HTH
Dan Artuso, Access MVP


I have a form that is filled in by data from a table and is
saved to
a
second table. This for creating an invoice from customer
table,
the
reason
that I do this is so that the invoice has the correct
details at
time of
creation. If the customer address changes the previously
created
invoice
holds the old address which was current at the time of
creation.

This is the code I use to fill in the form:



Dim MyDB As Database

Dim CustomerList As Recordset



Set MyDB = CurrentDb()

Set CustomerList = MyDB.OpenRecordset("Customer")



CustomerList.Index = "PrimaryKey"

CustomerList.Seek "=", Screen.ActiveControl



If CustomerList.NoMatch Then

DoCmd.GoToControl "Street"

Else

Me![CUSTOMERID] = [CustomerList]![CUSTOMERID]



Me![FName] = [CustomerList]![FName]

Me![Title] = [CustomerList]![Title]

Me![Street] = [CustomerList]![Street]

Me![City] = [CustomerList]![City]

Me![State] = [CustomerList]![State]

Me![PostCode] = [CustomerList]![PostCode]

Me![CompanyPhoneNumber] =
[CustomerList]![CompanyPhoneNumber]

Me![Terms] = [CustomerList]![Terms]

DoCmd.GoToControl "CustomerID"

End If






What I want to do is use a Query as the data source and lieu
of
a
Table

I have tried using QueryDef and QueryDefs but I have not got
anywhere.

Any help would be appreciated.



Regards,


John A