1. I have a table called tblOrder that stores data on orders placed.
It has various links to other tables, such as tblSupplier, tblProject, tblStaffMember.
(There are actually two relationships to tblStaffMember, as there are two fields in tblOrder called OrderedBy and AuthorisedBy)
I have a form called frmOrder based on tblOrder. On frmOrder I have combo boxes based on tblSupplier, tblProject and so on.
I would like to display two things with regards to cboProject (combo box for project), the ProjectNo and ProjectName. This is easy enough when using the drop down list of the combo box, but when the item is actually selected it only shows the data from the first colmun, i.e. ProjectNo.
So I have created a subform called subProjectName that is based on a query that looks at the ProjectNo selected in cboProject and returns the ProjectName. I have placed the "me.refresh" code in the after update property of cboProject.
However, if the user has not chosen the supplier, project, person who placed the order and the person who authorised the order, then you get an error message because "a related field is required in table...." or something like that.
To get around this, I have created a "N/A" record in each of the related tables and set the default values of the combo baxes to "N/A".
Although it works, is this the only way of doing this? Doesn't seem very professional! Any help will be gratefully received.
2. In tblOrder there is a memo field called GoodsReceived. This is where the user can enter comments regarding the goods that were received. Instead of placing this memo field on frmOrder, to save space I have created another: frmGoodsComments. The record source for this is tblOrder, but the only field on the form is GoodsComments.
On frmOrder there is a button with the following code:
On Error GoTo Err_cmdOpenFrmGoodsComments_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmGoodsComments"
stLinkCriteria = "[OrderNo]=" & Me![OrderNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenFrmGoodsComments_Click:
Exit Sub
Err_cmdOpenFrmGoodsComments_Click:
MsgBox Err.Description
Resume Exit_cmdOpenFrmGoodsComments_Click
At first, I was unable to type anything into the GoodsComments field on frmGoodsComments. I then realised that this is due to the fact that I was opening the same record on two different forms. So I set the Record Locks property to "No Locks".
The only annoying thing is that everytime I enter values in frmGoodsComments and then save or move to another record in frmOrder, I get a write conflict message because "another user has changed this record".
Does anyone have any idea how to stop this?!
Many thanks in advance.
It has various links to other tables, such as tblSupplier, tblProject, tblStaffMember.
(There are actually two relationships to tblStaffMember, as there are two fields in tblOrder called OrderedBy and AuthorisedBy)
I have a form called frmOrder based on tblOrder. On frmOrder I have combo boxes based on tblSupplier, tblProject and so on.
I would like to display two things with regards to cboProject (combo box for project), the ProjectNo and ProjectName. This is easy enough when using the drop down list of the combo box, but when the item is actually selected it only shows the data from the first colmun, i.e. ProjectNo.
So I have created a subform called subProjectName that is based on a query that looks at the ProjectNo selected in cboProject and returns the ProjectName. I have placed the "me.refresh" code in the after update property of cboProject.
However, if the user has not chosen the supplier, project, person who placed the order and the person who authorised the order, then you get an error message because "a related field is required in table...." or something like that.
To get around this, I have created a "N/A" record in each of the related tables and set the default values of the combo baxes to "N/A".
Although it works, is this the only way of doing this? Doesn't seem very professional! Any help will be gratefully received.
2. In tblOrder there is a memo field called GoodsReceived. This is where the user can enter comments regarding the goods that were received. Instead of placing this memo field on frmOrder, to save space I have created another: frmGoodsComments. The record source for this is tblOrder, but the only field on the form is GoodsComments.
On frmOrder there is a button with the following code:
On Error GoTo Err_cmdOpenFrmGoodsComments_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmGoodsComments"
stLinkCriteria = "[OrderNo]=" & Me![OrderNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenFrmGoodsComments_Click:
Exit Sub
Err_cmdOpenFrmGoodsComments_Click:
MsgBox Err.Description
Resume Exit_cmdOpenFrmGoodsComments_Click
At first, I was unable to type anything into the GoodsComments field on frmGoodsComments. I then realised that this is due to the fact that I was opening the same record on two different forms. So I set the Record Locks property to "No Locks".
The only annoying thing is that everytime I enter values in frmGoodsComments and then save or move to another record in frmOrder, I get a write conflict message because "another user has changed this record".
Does anyone have any idea how to stop this?!
Many thanks in advance.
