combo box lookup

[Guest]

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

Hi tboyce,

Try changing the DDlookup to;
=Dlookup("[Town]", "QryContacts Query", "[CustomerID] = " &
Me.cboCompany.column(1) & "")

or if CustomerID if Alphanumeric rather than just numbers;
=Dlookup("[Town]", "QryContacts Query", "[CustomerID] = '" &
Me.cboCompany.column(1) & "'")

hope this helps,

PS you might find it easier to make the form bound to the query you have
created, and then set the forms recordsource to match the selected record
from cboCompany eg;
Me.RecordSource = " SELECT * FROM QryContacts WHERE CustomerID = " &
me.cboCompany.Column(1) & "")
I would also rename your query (as above) so it doesn't have any spaces in
the name as this may cause you a headache later.
And I would change the bound column to CustomerID (column(0)) and have it's
width as 0cm.

TonyT..

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

Hi there Tony thank you for getting back to me. I have taken on board what
you said. I created a combo box query and called it FindQuery and bound
column 0 as CustomerID, which i have hidden and shows all the other relevent
data. In my text box underneath i entered this formula
=DLookUp("[Town]","QryFindContact","[CustomerID] = " &
Me.cboContactID.column(0) & "")
but still get Name?. What am i doing wrong. How can i bound the Quotation
form to a query when it is bound to a table trying to store the Quotation
Number, CustID and Date. I thought queries were read only results. If i could
make an input form based on query would be cool, if you could tell me how.
Appreciate your time dont give up on me. if you just write it down or send me
alink to a site where more help on this is available.

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

If you include the actual data you are trying to find ie Town in the
combobox's recordsource query, (have hidden by width 0) then you can simply
refer to the column number that is storing the information.

I would use vba code and put the following in the After_update event of the
combobox;
If Not ISNull(Me.cboContactID) Then
txtMyTextBoxForTown = Me.cboContactID.Column(4) '<enter column number
with Town as data source
End If

You can use a query as the entire recordsource for the form and it will
update all the associated tables, providing it is a simple query.

TonyT..

Hi there Tony thank you for getting back to me. I have taken on board what
you said. I created a combo box query and called it FindQuery and bound
column 0 as CustomerID, which i have hidden and shows all the other relevent
data. In my text box underneath i entered this formula
=DLookUp("[Town]","QryFindContact","[CustomerID] = " &
Me.cboContactID.column(0) & "")
but still get Name?. What am i doing wrong. How can i bound the Quotation
form to a query when it is bound to a table trying to store the Quotation
Number, CustID and Date. I thought queries were read only results. If i could
make an input form based on query would be cool, if you could tell me how.
Appreciate your time dont give up on me. if you just write it down or send me
alink to a site where more help on this is available.

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

I have created a new quotation Form now based on a query of quotation records
and quotation details seems fine cheers. Now i have created new combo boxes
and entered Private Sub ContactID_AfterUpdate()
If Not IsNull(Me.cboContact) Then
TextBox "Address" = Me.cboContactID.Column(2) '<enter column number
End Sub

with the same #Name?

If you include the actual data you are trying to find ie Town in the
combobox's recordsource query, (have hidden by width 0) then you can simply
refer to the column number that is storing the information.

I would use vba code and put the following in the After_update event of the
combobox;
If Not ISNull(Me.cboContactID) Then
txtMyTextBoxForTown = Me.cboContactID.Column(4) '<enter column number
with Town as data source
End If

You can use a query as the entire recordsource for the form and it will
update all the associated tables, providing it is a simple query.

TonyT..

Hi there Tony thank you for getting back to me. I have taken on board what
you said. I created a combo box query and called it FindQuery and bound
column 0 as CustomerID, which i have hidden and shows all the other relevent
data. In my text box underneath i entered this formula
=DLookUp("[Town]","QryFindContact","[CustomerID] = " &
Me.cboContactID.column(0) & "")
but still get Name?. What am i doing wrong. How can i bound the Quotation
form to a query when it is bound to a table trying to store the Quotation
Number, CustID and Date. I thought queries were read only results. If i could
make an input form based on query would be cool, if you could tell me how.
Appreciate your time dont give up on me. if you just write it down or send me
alink to a site where more help on this is available.

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

I have created a new quotation Form now based on a query of quotation records
and quotation details seems fine cheers. Now i have created new combo boxes
and entered Private Sub ContactID_AfterUpdate()
If Not IsNull(Me.cboContact) Then
TextBox "Address" = Me.cboContactID.Column(2) '<enter column number
End Sub

with the same #Name?

The correct syntax for populating the Address textbox would be,
Me.Address = Me.cboContactID.Column(2)

getting closer!!!

TonyT..

If you include the actual data you are trying to find ie Town in the
combobox's recordsource query, (have hidden by width 0) then you can simply
refer to the column number that is storing the information.

I would use vba code and put the following in the After_update event of the
combobox;
If Not ISNull(Me.cboContactID) Then
txtMyTextBoxForTown = Me.cboContactID.Column(4) '<enter column number
with Town as data source
End If

You can use a query as the entire recordsource for the form and it will
update all the associated tables, providing it is a simple query.

TonyT..

Hi there Tony thank you for getting back to me. I have taken on board what
you said. I created a combo box query and called it FindQuery and bound
column 0 as CustomerID, which i have hidden and shows all the other relevent
data. In my text box underneath i entered this formula
=DLookUp("[Town]","QryFindContact","[CustomerID] = " &
Me.cboContactID.column(0) & "")
but still get Name?. What am i doing wrong. How can i bound the Quotation
form to a query when it is bound to a table trying to store the Quotation
Number, CustID and Date. I thought queries were read only results. If i could
make an input form based on query would be cool, if you could tell me how.
Appreciate your time dont give up on me. if you just write it down or send me
alink to a site where more help on this is available.

:

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

Hello again, Have changed that, but the formula has produced the same result
#Name?. Does the address test box need any control or record source entering
because i have ignored the error on the text box control source at the mo but
didn't seem to make any difference to the formula. Any ideas PLEASE!!???

I have created a new quotation Form now based on a query of quotation records
and quotation details seems fine cheers. Now i have created new combo boxes
and entered Private Sub ContactID_AfterUpdate()
If Not IsNull(Me.cboContact) Then
TextBox "Address" = Me.cboContactID.Column(2) '<enter column number
End Sub

with the same #Name?

The correct syntax for populating the Address textbox would be,
Me.Address = Me.cboContactID.Column(2)

getting closer!!!

TonyT..

If you include the actual data you are trying to find ie Town in the
combobox's recordsource query, (have hidden by width 0) then you can simply
refer to the column number that is storing the information.

I would use vba code and put the following in the After_update event of the
combobox;
If Not ISNull(Me.cboContactID) Then
txtMyTextBoxForTown = Me.cboContactID.Column(4) '<enter column number
with Town as data source
End If

You can use a query as the entire recordsource for the form and it will
update all the associated tables, providing it is a simple query.

TonyT..


:

Hi there Tony thank you for getting back to me. I have taken on board what
you said. I created a combo box query and called it FindQuery and bound
column 0 as CustomerID, which i have hidden and shows all the other relevent
data. In my text box underneath i entered this formula
=DLookUp("[Town]","QryFindContact","[CustomerID] = " &
Me.cboContactID.column(0) & "")
but still get Name?. What am i doing wrong. How can i bound the Quotation
form to a query when it is bound to a table trying to store the Quotation
Number, CustID and Date. I thought queries were read only results. If i could
make an input form based on query would be cool, if you could tell me how.
Appreciate your time dont give up on me. if you just write it down or send me
alink to a site where more help on this is available.

:

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

It now wont let me out of the visual screen, it has picked up Me.Address =
cboContactID.column(2) and shows the answer that i am looking for in the
quick help bar but the debugger is saying that i cant enter a value in this
field. Although it is an unbound field with no datatype set to it as far as i
know. I am feeling it you know.

I have created a new quotation Form now based on a query of quotation records
and quotation details seems fine cheers. Now i have created new combo boxes
and entered Private Sub ContactID_AfterUpdate()
If Not IsNull(Me.cboContact) Then
TextBox "Address" = Me.cboContactID.Column(2) '<enter column number
End Sub

with the same #Name?

The correct syntax for populating the Address textbox would be,
Me.Address = Me.cboContactID.Column(2)

getting closer!!!

TonyT..

If you include the actual data you are trying to find ie Town in the
combobox's recordsource query, (have hidden by width 0) then you can simply
refer to the column number that is storing the information.

I would use vba code and put the following in the After_update event of the
combobox;
If Not ISNull(Me.cboContactID) Then
txtMyTextBoxForTown = Me.cboContactID.Column(4) '<enter column number
with Town as data source
End If

You can use a query as the entire recordsource for the form and it will
update all the associated tables, providing it is a simple query.

TonyT..


:

Hi there Tony thank you for getting back to me. I have taken on board what
you said. I created a combo box query and called it FindQuery and bound
column 0 as CustomerID, which i have hidden and shows all the other relevent
data. In my text box underneath i entered this formula
=DLookUp("[Town]","QryFindContact","[CustomerID] = " &
Me.cboContactID.column(0) & "")
but still get Name?. What am i doing wrong. How can i bound the Quotation
form to a query when it is bound to a table trying to store the Quotation
Number, CustID and Date. I thought queries were read only results. If i could
make an input form based on query would be cool, if you could tell me how.
Appreciate your time dont give up on me. if you just write it down or send me
alink to a site where more help on this is available.

:

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.

 

[Guest]

Just delete the address textbox and put a new textbox in it's place, call it
txtAddress, incase their is confusion over another bound field name.

this time i'm sure..........well fairly confident!

TonyT..

It now wont let me out of the visual screen, it has picked up Me.Address =
cboContactID.column(2) and shows the answer that i am looking for in the
quick help bar but the debugger is saying that i cant enter a value in this
field. Although it is an unbound field with no datatype set to it as far as i
know. I am feeling it you know.

I have created a new quotation Form now based on a query of quotation records
and quotation details seems fine cheers. Now i have created new combo boxes
and entered Private Sub ContactID_AfterUpdate()
If Not IsNull(Me.cboContact) Then
TextBox "Address" = Me.cboContactID.Column(2) '<enter column number
End Sub

with the same #Name?

The correct syntax for populating the Address textbox would be,
Me.Address = Me.cboContactID.Column(2)

getting closer!!!

TonyT..

:

If you include the actual data you are trying to find ie Town in the
combobox's recordsource query, (have hidden by width 0) then you can simply
refer to the column number that is storing the information.

I would use vba code and put the following in the After_update event of the
combobox;
If Not ISNull(Me.cboContactID) Then
txtMyTextBoxForTown = Me.cboContactID.Column(4) '<enter column number
with Town as data source
End If

You can use a query as the entire recordsource for the form and it will
update all the associated tables, providing it is a simple query.

TonyT..


:

Hi there Tony thank you for getting back to me. I have taken on board what
you said. I created a combo box query and called it FindQuery and bound
column 0 as CustomerID, which i have hidden and shows all the other relevent
data. In my text box underneath i entered this formula
=DLookUp("[Town]","QryFindContact","[CustomerID] = " &
Me.cboContactID.column(0) & "")
but still get Name?. What am i doing wrong. How can i bound the Quotation
form to a query when it is bound to a table trying to store the Quotation
Number, CustID and Date. I thought queries were read only results. If i could
make an input form based on query would be cool, if you could tell me how.
Appreciate your time dont give up on me. if you just write it down or send me
alink to a site where more help on this is available.

:

I wish to create a database for my brother in law. He installs large CCTV
Camera systems for local Councils, in schools, College's, Industrial Estates,
and such like. Before he gets the job start. He has to compile a quotation
based on proposed cameras, towers and broadcast equipment, this gets assessed
before, maybe being amended before a quote is accepted and then he produces
an invoice to bill the Council. He was producing these documents in Word and
meant a lot of copying and pasting from old doc's to produce the finished
articles. I had done an ECDL Advanced Access course, and thought I could
knock him a database up in no time. Big under estimation of what was
involved. Basically, I created a contact table, Sales, Products and Quotation
tables. No quotation can be built without adding a new contact in the
relevant table first. Then, I copied the invoice table and called it
quotation table as in the business the quotation is built before the invoice
is agreed. Hope you following so far. I was hoping that, by entering the
contact names and products into their relevant tables I could recall this
information without the need to type it in, making the data available to the
database itself. Once I finished the tables I created the forms to input the
data necessary to produce firstly, the quotation record (fields Quotation No,
Date, Customer ID) and a sub table which shows the details of the products
Prod Name, Cost Prod details, qty req, (Line no & Quote no, not shown).
Problem starts here. I created a query just based on the contacts table.
Showing Company Name, Contact ID, Address, Post Code, etc, to enable the
placement of a combo box that uses the query to select and show the company
name (Column 0 bound) with column 1 being the contact id. This is just a
tester to see if I could make it work, which it does fine. Under the combo
box I have placed a text box and entered the dlookup formula to check the
query and return the town and post code. Like so =Dlookup("[Town]",
"QryContacts Query", " [CustomerID] = [cboCompany].column(1)") which it
doesn't, I get a Name? problem. Really I like the idea of a combo box lookup
, as it returns a value much more quickly, still a Name? issue though, like
so =[cboContacts Query].column(4)
I have also tried it on the sub form with the same results being produced. I
don’t see what else I can do, nobody can tell me about combo box lookups or
dlookups for that matter and am stuck fast. Please help me.