multiple issues with access form

[Dara Hunter]

Newbie here...

I have one table.
Customer = txt
SOW_Num = num
Description = txt
Acct_Mgr = txt
etc.

I have combo box for the customer. User selects a customer that value is stored in an invisible txtbox. Once user has selected a customer the sow combo box will only show sow's for that customer. Once a user selects a sow number that value is stored in an invisible txt box. I have a submit button that does dlookups for the rest of the fields on the form based on the txt boxes.

My issues:
1) It seems that anytime I add data to a txt field that has a dlookup on it, that information gets automatically added to the table w/out me having to press save. How can I prevent this?

2) I have combine txt box for the contact first and last name =[Customer_Contact_First] & ' ' & [Customer_Contact_Last]. I have a clear_form button that sets all boxes to empty strings. This is the only one that will not reset to empty. I tried doing them individually but that doesn't work. Suggestions?

3)Dlookups are not working for check boxes. Is there something else I should use for a yes/no field? Here is my code for one check box not working:
Me.cbx_completed.Value = DLookup("[cbx_completed]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])


4)Largest issue. I have an archive check box. (it is a yes/no check box in the table).I have an if statement in the submit btn that does the dlookups. The submit button looks up the value of the archive button, if it no then continue with the lookups for the other fields, if it is yes then an msg box appears with an error stating that this record is archived.

It does nothing. If I remove the if statement the dlookups work. Here is my code for that:

Me.cbx_Archive.Value = DLookup("[cbx_Archive]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])
Dim MyString

'If cbx_Archive.Value = vbNo Then
'MyString = "No"
Me.Customer_Contact_First.Value = DLookup("[Customer_Contact_First]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])
Me.Customer_Contact_Last.Value = DLookup("[Customer_Contact_Last]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])

'Else
'MyString = "Yes" 'do nothing
'End If

I have removed most of the lookups so this post is not huge. I think it has something to do with the dlookup not working for yes/no check box field but I am not sure what to do.

Please Help!

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[Steve Sanford]

Hi Dara,

Lets start with #4. You have

'If cbx_Archive.Value = vbNo Then

If cbx_Archive is a check box (True/False), then comparing it to the
constant "vbNO" will *always* be FALSE. In the Immediate Window (control-G),
if you enter

? vbNo

and press enter, the result will be 7. If you enter

? vbYes

and press enter, the result will be 6.

You need to use vbTrue or vbFalse (or TRUE or FALSE) for a check box, since
a check box (most of the time) can only be true or false. (Try ? vbTrue
in the Immediate Window or ? vbYes = vbTrue )

-----------------------
#2) Are you using a query for the recordsource of the form? If not, you
should try it. With a query, the records can be sorted and you can create a
calculated column for the customer contact full name. Instead of have a
control with "=[Customer_Contact_First] & ' ' & [Customer_Contact_Last]",
the control's control source would be the name of the column in the query.

-----------------------
#3) Dlookups() are slow and limiting. It looks like the lookups are all on
"tbl_ALL_SOW", with arguments from controls on the form "[frm_Update_SOW2]".
Instead, I would try opening a recordset and getting all of the data at the
same time.


-----------------------
#1) Not too sure about this one. It sounds like the text box is bound to a
field in the form record source.

1) It seems that anytime I add data to a txt field that has a dlookup on it,

The control (text box) doesn't have a "dlookup on it", just code that fills
the text box when the Sub is executed. You can type anything in the text box
and it is entered into the selected (active) record. You would need to not
allow edits or lock the control to keep from accidently changing the text box.


BTW, "Value" is the default property for controls and doesn't need to be
type. Instead of Me.cbx_completed.Value, you can just use Me.cbx_completed.
Saves on typing.........



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Newbie here...

I have one table.
Customer = txt
SOW_Num = num
Description = txt
Acct_Mgr = txt
etc.

I have combo box for the customer. User selects a customer that value is stored in an invisible txtbox. Once user has selected a customer the sow combo box will only show sow's for that customer. Once a user selects a sow number that value is stored in an invisible txt box. I have a submit button that does dlookups for the rest of the fields on the form based on the txt boxes.

My issues:
1) It seems that anytime I add data to a txt field that has a dlookup on it, that information gets automatically added to the table w/out me having to press save. How can I prevent this?

2) I have combine txt box for the contact first and last name =[Customer_Contact_First] & ' ' & [Customer_Contact_Last]. I have a clear_form button that sets all boxes to empty strings. This is the only one that will not reset to empty. I tried doing them individually but that doesn't work. Suggestions?

3)Dlookups are not working for check boxes. Is there something else I should use for a yes/no field? Here is my code for one check box not working:
Me.cbx_completed.Value = DLookup("[cbx_completed]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])


4)Largest issue. I have an archive check box. (it is a yes/no check box in the table).I have an if statement in the submit btn that does the dlookups. The submit button looks up the value of the archive button, if it no then continue with the lookups for the other fields, if it is yes then an msg box appears with an error stating that this record is archived.

It does nothing. If I remove the if statement the dlookups work. Here is my code for that:

Me.cbx_Archive.Value = DLookup("[cbx_Archive]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])
Dim MyString

'If cbx_Archive.Value = vbNo Then
'MyString = "No"
Me.Customer_Contact_First.Value = DLookup("[Customer_Contact_First]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])
Me.Customer_Contact_Last.Value = DLookup("[Customer_Contact_Last]", "tbl_ALL_SOW", _
"[Customer] = '" & Forms![frm_Update_SOW2]![tbx_Customer] _
& "' And [SOW_Num] = " & Forms![frm_Update_SOW2]![tbx_SOW_Num])

'Else
'MyString = "Yes" 'do nothing
'End If

I have removed most of the lookups so this post is not huge. I think it has something to do with the dlookup not working for yes/no check box field but I am not sure what to do.

Please Help!

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx