db design questions

[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

 

[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.

 

[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

 

[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

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

 

[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

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

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

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