db design questions

L

lewie

I have 2 questions. First I have a main table where all my po's will be
stored. I have lookup tables linked to those fields through ID's. Data
stored in the table is int like 1 stored in request field. then there
is a request table with a 1 requestid and a second field requesttext. i
used the lookup field in the table.made it combo box count 2, linked
to 1, amd view is 0",1". so when u look at table you see the
description. I have a form where i want to display the text info. How
is the right way to display this data. Do i need the form to use a
query as the recordsource and place both the main table and lookup
table in the query so I can see the text I need. what is the right way
to do this. and Second I autofill this form from code. How do I get at
that data in the lookup table to assign it to the form.I tried making a
recordset of the table but the field is basically an integer it assigns
the field on the form to an integer and somehow it changes my lookup
table value.
So in summary I would like to know what is the accepted method to
design these interconnections: main table to lookup table and what
value is placed in the form and how is data in lookup table protected
from being changed?
Thanks in advance.
Lewie
 
D

Duane Hookom

First of all, many of us veteran developers hate the lookup field "feature".
Consider reading http://www.mvps.org/access/lookupfields.htm.

For forms, I don't include the lookup table in the form's record source. I
use combo boxes that use the lookup table as the row source.

I'm not sure how you are "autofilling" the form from code. Continue using a
combo box and set the control value to the "ID" field.
 
L

lewie

Thanks Duane.
I took your and the experts advice and dropped the lookup in the table
and used combo box to display on the form. So now this form is used
for ordering. I query a table and generate a list of titles that need
to be ordered. I check a lookup table that checks the type and studio
and from that determines what the contents of several fields should be
and attempts to autofill. I am trying to autofill the combobox and
herein lies my problem....... If I use a number (type in table to be
stored) I get a type mismatch and if i assign the text (type stored in
combobox) I get a type mismatch
Do Until rst1.EOF
studioname = (rst1.Fields("Studioname"))
If (rst1.Fields("copytype") = tapeid) And
(studioname = Studio) Then
my_answer = InputBox(" Is " +
rst1.Fields("Contactname") + " the correct contact for " + studioname +
" " + tapetype + " y or n") ' Calculate the cc
If (my_answer Like "y*") Then
Request1 = rst1.Fields("request")
Exit Do
Else
DoCmd.OpenForm "Enter contact", acNormal, ,
, , acDialog
newcontactid = Forms![enter
contact]!contact1
'MsgBox "Please update table"
DoCmd.close acForm, "Enter contact", acSave
Exit Do

End If

End If
rst1.MoveNext
Loop
do i nedd a different property of the combo box other than the value to
assign what I need?
I'm kinda stumped.
Thanks for your quick response.
Lewie
 
D

Duane Hookom

I don't know what your issue is. I don't know what studioname is in the
second line. If this is a control, I would expect a prefix of "Me.txt" or
"Me.cbo" or whatever. If it is memory variable I would expect
"strStudioName" if it was text.

Also, don't use "+" to concatenate your strings. Use expressions like " Is "
& rst1.Fields("copytype")...

You should also replace the InputBox with MsgBox like:

If MsgBox("Ask your question", vbYesNo+vbQuestion,"What will it be") = vbYes
then

I don't know where your combo box fites into your code. The combo box should
have a row source of your look up table.

--
Duane Hookom
MS Access MVP

lewie said:
Thanks Duane.
I took your and the experts advice and dropped the lookup in the table
and used combo box to display on the form. So now this form is used
for ordering. I query a table and generate a list of titles that need
to be ordered. I check a lookup table that checks the type and studio
and from that determines what the contents of several fields should be
and attempts to autofill. I am trying to autofill the combobox and
herein lies my problem....... If I use a number (type in table to be
stored) I get a type mismatch and if i assign the text (type stored in
combobox) I get a type mismatch
Do Until rst1.EOF
studioname = (rst1.Fields("Studioname"))
If (rst1.Fields("copytype") = tapeid) And
(studioname = Studio) Then
my_answer = InputBox(" Is " +
rst1.Fields("Contactname") + " the correct contact for " + studioname +
" " + tapetype + " y or n") ' Calculate the cc
If (my_answer Like "y*") Then
Request1 = rst1.Fields("request")
Exit Do
Else
DoCmd.OpenForm "Enter contact", acNormal, ,
, , acDialog
newcontactid = Forms![enter
contact]!contact1
'MsgBox "Please update table"
DoCmd.close acForm, "Enter contact", acSave
Exit Do

End If

End If
rst1.MoveNext
Loop
do i nedd a different property of the combo box other than the value to
assign what I need?
I'm kinda stumped.
Thanks for your quick response.
Lewie
Click to expand...
 
L

lewie

I just put it back the way it was and i got everything working. You
can't use me in a code module and I was trying to set the values in
the combo box and it didn't work.
I tried to use cbobox.column (1) but apparently that is a read only
property. I could assign it to a variable but I couldn't assign a value
to it. also the combo boxes replaced memo fileds and just didn't seem
to work right. I use + because I thought it drops out everything if a
member is null which is what i want.
Sorry for wasting your time.
Thanks.
lewie

Duane said:
I don't know what your issue is. I don't know what studioname is in the
second line. If this is a control, I would expect a prefix of "Me.txt" or
"Me.cbo" or whatever. If it is memory variable I would expect
"strStudioName" if it was text.

Also, don't use "+" to concatenate your strings. Use expressions like " Is "
& rst1.Fields("copytype")...

You should also replace the InputBox with MsgBox like:

If MsgBox("Ask your question", vbYesNo+vbQuestion,"What will it be") = vbYes
then

I don't know where your combo box fites into your code. The combo box should
have a row source of your look up table.

--
Duane Hookom
MS Access MVP

lewie said:
Thanks Duane.
I took your and the experts advice and dropped the lookup in the table
and used combo box to display on the form. So now this form is used
for ordering. I query a table and generate a list of titles that need
to be ordered. I check a lookup table that checks the type and studio
and from that determines what the contents of several fields should be
and attempts to autofill. I am trying to autofill the combobox and
herein lies my problem....... If I use a number (type in table to be
stored) I get a type mismatch and if i assign the text (type stored in
combobox) I get a type mismatch
Do Until rst1.EOF
studioname = (rst1.Fields("Studioname"))
If (rst1.Fields("copytype") = tapeid) And
(studioname = Studio) Then
my_answer = InputBox(" Is " +
rst1.Fields("Contactname") + " the correct contact for " + studioname +
" " + tapetype + " y or n") ' Calculate the cc
If (my_answer Like "y*") Then
Request1 = rst1.Fields("request")
Exit Do
Else
DoCmd.OpenForm "Enter contact", acNormal, ,
, , acDialog
newcontactid = Forms![enter
contact]!contact1
'MsgBox "Please update table"
DoCmd.close acForm, "Enter contact", acSave
Exit Do

End If

End If
rst1.MoveNext
Loop
do i nedd a different property of the combo box other than the value to
assign what I need?
I'm kinda stumped.
Thanks for your quick response.
Lewie

Duane said:
First of all, many of us veteran developers hate the lookup field
"feature".
Consider reading http://www.mvps.org/access/lookupfields.htm.

For forms, I don't include the lookup table in the form's record source.
I
use combo boxes that use the lookup table as the row source.

I'm not sure how you are "autofilling" the form from code. Continue using
a
combo box and set the control value to the "ID" field.

--
Duane Hookom
MS Access MVP

I have 2 questions. First I have a main table where all my po's will be
stored. I have lookup tables linked to those fields through ID's. Data
stored in the table is int like 1 stored in request field. then there
is a request table with a 1 requestid and a second field requesttext. i
used the lookup field in the table.made it combo box count 2, linked
to 1, amd view is 0",1". so when u look at table you see the
description. I have a form where i want to display the text info. How
is the right way to display this data. Do i need the form to use a
query as the recordsource and place both the main table and lookup
table in the query so I can see the text I need. what is the right way
to do this. and Second I autofill this form from code. How do I get at
that data in the lookup table to assign it to the form.I tried making a
recordset of the table but the field is basically an integer it assigns
the field on the form to an integer and somehow it changes my lookup
table value.
So in summary I would like to know what is the accepted method to
design these interconnections: main table to lookup table and what
value is placed in the form and how is data in lookup table protected
from being changed?
Thanks in advance.
Lewie
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Crosstab Query to Existing Table 0
Appending tables 0
comparing table values 1
Sorting Multiple Tables 1
How to have two tables on same line? 1
Help creating a search form in Access XP 0
Dates In Excel 5
Creating a Search using a combo box and then creating a report. 4

Top