Best Method Handling Billto ID And Shipto ID
Mar 25, 2007
I would like to see if I am doing this okay. I want to confirm the best manner of storing a BilltoID and a ShiptoId address.
I presently store 1,000 BilltoID's with 1 address each record in table1. Then I have 1,500 ShiptoID's with 1 address each record in table2.
I figure that i would make a main form with a pulldown combo box on the left for soldtoID and have an address travel in from table1. and then a different set to the right with another pulldown combo box for the shipto address pouring in from table 2.
I have looked at some models. they seem to have the BilltoID address is linked to a table1 like mine with one address in a combo box. However the Shipto address section in their samples is a Manual input section and not a table.
So is my method better? That is, using 2 tables and 2 combo boxes on the form.
Unrelated to my Order form, i want to mention that I also have a Contact table with many people who could get letters from us. so i have more address over in that table using 2 fields in this contact table (billtoID and shiptoID). therefore, this Contact table can be used for a subform and connect to a main form using those 2 fields as parent child thing.
thanks you for reading this and giving suggestions before i proceed.
View Replies
ADVERTISEMENT
Feb 12, 2012
I have my Data Types sent to "Date/Time" and a format YYYY/MM/DD.
What is a good method of handling approximate dates? IE: Early April 2012, or ~5/14/2012, or TBD.
Eventually, I want to create queries to calculate dates against each other.
View 3 Replies
View Related
Nov 23, 2006
I have recently upsized an Access database to SQL Server. The .adp is for the most part working fine.
However, when an end user tries to create a duplicate record in one of the Primary key columns of a Form he gets a SQL Server error message as follows:
"Violation of Primary Key constraint. Cannot insert duplicate key in object myTable".
This is obviously not a very useful error message. What would be the best way to ensure that a more useful error message is returned?
I have considered adding code to the "After_Update" event of the cell in question. This however seems long winded. Is there an easier option?
Thanks
Kabir
View 1 Replies
View Related
Dec 3, 2006
Hi
Have the following code that works on Access 2000
Dim myForenameBefore As String
Dim myForenameAfter As String
With CurrentDb.OpenRecordset("Names", dbOpenDynaset)
Do Until .EOF
myForenameBefore = .Fields("Forename")
myForenameAfter = Replace(.Fields("Forename"), "Elizh", "Elizabeth")
MsgBox myForenameBefore & " " & myForenameAfter
.MoveNext
Loop
.Close
End With
So that I can replace any abbreviated names in a table, however I cannot figure out how to put the value of myForenameAfter back into the table replacing the existing data.
Must be particularly thick today
View 5 Replies
View Related
Oct 18, 2005
I have a form with a number of check boxes. Clicking on the checkboxes carries out a count function which is then displayed. If the count goes above 20 then a message box is displayed telling the user of it. My problem is that once the number is above 20, the message box diplays everytime. I want it to display just once, regardless of how high the number goes. The checkboxes can be ticked in any order.
At present i'm using
On click
If Me.Text1 > 20 Then
Msgbox "The number is graeter than 20"
End If
View 1 Replies
View Related
May 12, 2005
Hi all
The following code only half works. I want to stop an ugly error occuring when primary keys are duplicated. In the following code the error handler picks up and displays a simple message when a user attempts to add duplicate values. BUT if the users adds a record, the record is successfully added followed with the same error message. I think I've put the error handler in the wrong place, but I can't figure out where it should go. Any ideas are much appreciated.
Damon
Private Sub cmdUpdateGoMaths_Click()
'update Go Maths
On Error GoTo error
Dim adors As ADODB.Recordset
Dim sSQL As String
' sSQL = "Select * from datGoMaths;"
' Set rsADO = New ADODB.Recordset
' rsADO.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
sSQL = "dataGoMaths"
Set adors = New ADODB.Recordset
adors.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With adors
.AddNew
.Fields("EQID") = Me.lstGoMathsStudentName
.Fields("IDGoMaths") = Me.lstUnitNo.Column(0)
.Fields("Score") = Me.txtScore
.Update
End With
Set adors = Nothing
MsgBox Me.txtStudentName & " Added"
Me.lstGoMathsStudentName = ""
Me.txtScore = ""
error:
MsgBox "test"
DoCmd.CancelEvent
End Sub
View 5 Replies
View Related
Apr 30, 2006
I currently have a macro that sets the value of several fields on a "lost focus " event; however there is an error that I would like access to ignore, is there any way to have this macro process this function "On Error Resume Next" ?
Carbo
View 1 Replies
View Related
Jun 29, 2006
When I export my access database to XML I noticed that the XML elements for the table keys contain the key numbers themselves, not the table name (or a chosen field in the table). For an application I am working on, this presents a problem. What I have is:
One table called 'Process' that looks like:
Process instance_idSystem instance IdProcess name
26 24 Two_thread_process
I have another table called "Thread":
Thread _instance_id Process instance_Id Thread instance name
30 26 T1_instance
XML export looks like:
- <process_INSTANCE>
<process_instance_id>26</process_instance_id>
<system_instance_name_id>24</system_instance_name_id>
<process_instance_name>two_thread_process</process_instance_name>
- <thread_INSTANCE>
<Thread_instance_id>30</Thread_instance_id>
<process_instance_id>26</process_instance_id>
<thread_instance_name>T1_instance</thread_instance_name>
</thread_INSTANCE>
</process_INSTANCE>
What I would *like * the XML to look like is:
- <process_INSTANCE>
<process_instance_id>26</process_instance_id>
<system_instance_name_id>24</system_instance_name_id>
<process_instance_name>two_thread_process</process_instance_name>
- <thread_INSTANCE>
<Thread_instance_id>30</Thread_instance_id>
<process_instance_id>two_thread_process</process_instance_id> (**name instead of key here**)
<thread_instance_name>T1_instance</thread_instance_name>
<thread_instance_type>periodic</thread_instance_type>
</thread_INSTANCE>
The access XML export writes out the key values as they appear in the tables. What I would like is for it to put in the ‘process instance name’ instead of the key that points back to the base (process) field.
Any pointers on how I could do this?
Thanks
John
View 3 Replies
View Related
Apr 12, 2007
Does anyone have a recommended way to deal with images (ie. linked to file, OLE object embedded, others I'm not aware of)???
I have a report which summarizes the history of a project. In this report I have a subreport which has task information. As a user enters task information they have the option of associating pictures with the task (ie. prototypes, design images, test setup, etc).
So my data structure is based on:
- many projects
- each project can have many tasks
- each task can have many pictures
My report for each project lists some typical project information and a subreport shows the task details associated with that project along with as many images that are associated with it.
My current system links to files on a network to avoid database size. This works well for storage and form use, until I try to print or preview a report. Then some of my pictures don't show up and I get a message that says images may be too large. My report could conceivably have up to 100 images, but I'm images stop showing up when I have around 15-20 pictures. Pictures files are typically 300 KB.
Is there a better way to show images on a report?????
View 6 Replies
View Related
Mar 2, 2005
I have a form where the user enters a year then clicks on a command button to open a report.
If a year that there is no data for is entered an error 2427 occurs.
I have tried putting the following code in the on error event of the form with now success:
Select Case DataErr
Case 2427
MsgBox "There are no records for the year you entered" & Chr(13) & "Make sure you entered the correct year”
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select
Does anyone know how to trap this error?
Thanks
View 2 Replies
View Related
Apr 25, 2006
Hi All,
I am rather new to this and I must admit I am getting everything completely wrapped around my head!
I am developing a database that stores technical support requests received and their solutions. I have three main tables :
Problem - stores all the details of the problems received
Client - stores the address details of the client which is then associated with the 'Problem' table
Company - stores the name of the company which is then associated with the 'Client' table. This is a separate table as different clients can work for the same company.
At present, I have a 'problem data entry' form in which I enter all the details of the problem. At the top there is a section where I can do a search for a particular client and company and the details appear in the respective text boxs on the 'problem data entry' form. If the client does not exist, I have a button that I use to open another form which allows the entry of the new client's details ('New Client'). The user can choose which company this client works for by using a combobox. If the company is not already in the database, I have another button for opening a 'New Company' form!
Its all a little convoluted and it does not work particularly well to be honest. When I add a new company, I need to move forward a record and then back in the 'New Company' form in order that the new company information is saved correctly.
The company selection combobox in the form 'New Client' is then updated with the new company name BUT I need to actually open the combobox and select the company name myself for it to work properly! Once again I also need to move forward one record and then back again in order to properly save all the information before I can close the form.
What would be nice is to do a search for the the company in the combobox on the 'New Client' form, if the company is not found, it automatically adds the company to the database whilst updating all the necessary links. Hence getting rid of the 'New Company' form. I would also like to get rid of the problem of having to move forwards a record and then back again.
Any ideas? I find my method to be rather poor, there must be a simpler way of doing this!
Many Thanks,
Lee
View 1 Replies
View Related
Aug 8, 2006
On a form i have a textbox to enter a date and multiple command buttons to open other forms.
After entering a date value, followed by either a tab or clicking on one of the command buttons, i want to validate the entered date value. If the date is invalid i want to display an error message and the cursor positioned on the textbox field.
The LostFocus event of the textbox is as follows:
Private Sub txtDateField_LostFocus()
If Not IsDate(txtDateField.Value) Then
MsgBox "Date invalid."
txtDateField.SetFocus
End If
End Sub
If entering an invalid date is followed by a tab, the error is displayed but the cursor is not positioned on the textbox field.
If entering an invalid date is followed by clicking on one of the command buttons, the error is displayed but the expected form is opened.
How to position the cursor appropriate and how to prevent opening the form?
Who helps?
View 1 Replies
View Related
Aug 8, 2006
On a form i have a textbox to enter a date and multiple command buttons to open other forms. After entering a date, either followed by a tab or clicking on one of the command buttons, i want the date validated. In case of an invalid date an error must be displayed and the cursor positioned to the textbox.
Therefor the LostFocus event is as follows:
Private Sub txtDateField_LostFocus()
If Not IsDate(txtDateField.Value) Then
MsgBox "Date invalid."
txtDateField.SetFocus
End If
End Sub
If entering an invalid date is followed by a tab the error is displayed but the cursor is not positioned to the textbox.
If entering an invalid date is followed by clicking on one of the command buttons the error is displayed but a form is opened.
How to position the cursor correctly and prevent opening of a form?
View 2 Replies
View Related
Oct 25, 2006
How do you do an Access/VBE split screen? I want to be able to watch the code execute as I enter data into the form.
I have redesigned a database, essentially building a new one from scratch. I have all of the forms created and they are working beautifully. :) Now, I'm error-handling to create user-friendly messages. I have a superb Access programming book, but it only briefly mentions error handling and doesn't describe at all how to do it. Most of my forms are pretty simple, and I've been able to complete all of the error-handling tasks using the Form Error event as well as coding other events with the error message number. However, the main form is complex and includes a function in a standard code module and a custom sub routine.
TIA!
AA
View 2 Replies
View Related
Sep 13, 2004
I have a button on a Tab that opens an input form to allow user to add a records. The record is indexed to relevant option from the combo bos on main form, so if no choice is made and user clicks add button megbox 'Invalid Use of Null' pops up. However I can't seem to prevent it, even when I've removed the code completely from the method/function. What's going on there? I just want the message box to say, please make a selection or something. Any ideas?
View 1 Replies
View Related
Nov 1, 2004
I have written the following Function as part of an Access program:
Public Function TotalWl() As Long
Dim sqlWL As String
Dim rsWL As Recordset
Dim dbWL As Database
Dim sendlDate As Date
Set dbWL = CurrentDb
sqlWL = "SELECT compul FROM qryStatisticsWL WHERE sendDate between 01/09/2004 and 30/10/2004 " 'this is all written on one line because I do not know how to split it in more than 'one lines
Set rsWL = dbWL.OpenRecordset(sqlWL, dbOpenSnapshot)
If Not rsWL.EOF Then
rsWL.MoveLast
TotalWl = rsWL.RecordCount
End If
End Function
When I call this function the result is always 0. The program does not like :
WHERE sendDate between 01/09/2004 and 30/10/2004
I have tried: WHERE sendDate >= 01/09/2004 and sendDate <= 30/10/2004
also: WHERE sendDate >= 01/09/2004 and <= 30/10/2004
I also used single quotes, like '01/09/2004 '
But nothing solved the problem. By the way, thee are data between these two dates.
Any suggestions how to proceed?
View 2 Replies
View Related
Aug 19, 2005
I have a database for which the user can "attach" supporting files. Currently I handle this by putting a button on the form. The button opens the folder related to the current record via windows explorer. The user is then free to open any file contained, or drag and drop new files if needed. It works reasonably well.
But there is no indication when you look at the form if there are any files attached or not.
First, if anyone can point me to a better way to handle this, (it bugs me that there are thousands of folders being created to each hold a file or two), I would appreciate that. The typical attachment is a message dragged and dropped out of Outlook.
Second, assuming I continue doing things this way, I am thinking I would like to add a listbox to the form. When the form moves to a new record, I would populate the list box with the name of any files found. I know how to do that, but to which event would I be attaching this code?
Thanks,
David
View 1 Replies
View Related
Oct 19, 2006
hi
The database XXXXX_20061018.mdb contains the form Frm_XXXXXX.
Currently this form does not show data after it has been opened.
infact of the Me.FilterOn = False.
so please give me some idea how to solve this problem
thanking you
View 6 Replies
View Related
Jan 16, 2006
I have to give a three paramters queries. But i have to handle null responses also in such a way that in one parameter or two parameters i don't want to enter value and the result should bring all possible values with the parameters I entered
Like customer, status and order number in different fields
when i don't enter order number the result should bring all values in status and customer
View 1 Replies
View Related
Oct 12, 2007
Just a quick one.
I'm not usre how to handle the error when cancelling a report. so when I hit a control to bring up a report and say I cancel it before I put in any parameters for example I get the dialog box " runtime error" "OpenReport action was cancelled" etc.
Could anyone help me on how to handle this with VBA so that it's at least a more user friendly dialog box?
thanks
View 3 Replies
View Related
Feb 5, 2008
I have a Access FE linked to a Access BE on a computer. Whenever the user of that computer shuts off, the Access FE handles this in worst way possible: It spews a zillion dialog warning of 'disk or network not found' before my error handler even takes over.
Has anybody come up with a way to catch such situation?
View 2 Replies
View Related
Jul 11, 2006
Hello,
I am building a Db that will report against individual transactions. Each transaction will be on a specific day.
Now I will need to slice and dice the data in a number of different ways depending on what the end user wants to see.
Therefore I need to build a date table that will aggregate all the individual days by Week, Month, calendar year, fiscal year...etc.
Being a rookie, I was simply going to build a table that has all the days for the last three years and then the corresponding weeks, months etc in columns to the right.
Because of this design, I will have over 1,000 records. Could I do it differently to keep the size of the table down?
For example use ranges as a cell value (Our week 1 is named "Feb 1st Wk", it is 1/30/06-2/5/06 for fiscal Yr 2006, 1/31/04-2/6/05 for fiscal Yr 2005...etc)
Any advice on this?
Thanks, Paul
View 5 Replies
View Related
Aug 6, 2015
I've got a problem with a loop for producing worksheets (reports). When everything works there's no problem but when one of the reports namely rptChemoOralWorksheet doesn't have data available to produce the report the loop finishes and then all remaining worksheets don't print.I've put in a standard error handler with MsgBox, but it's never triggered. I've also tried
Code:
On Error Resume Next
but again rather than skip the DoCmd.OpenReport "rptChemoOralWorksheet" (when the error is present) and move to the next one to continue printing through the list the loop stops and the subsequent code starts to run.
Code:
Dim mydatabase As DAO.Database
Dim rst As DAO.Recordset
Set mydatabase = CurrentDb
Set rst = mydatabase.OpenRecordset("atblChemoWSLoop", dbOpenDynaset)
Dim rsBN As String
Dim rsDose As Double
[code]....
View 4 Replies
View Related
Sep 19, 2012
I am taking a filing index of City Council Resolutions which is currently based in Excel and transferring the index to an Access database I am building. An issue I am having is that the Resolutions being indexed have a subject description that ranges from about 100 characters to +700. The new Access database must hold the complete contents of the Excel spreadsheet.
The direction I have taken is to divide up the subject field into three text fields as needed ([Subject1], [Subject2], [Subject3]) each with up to 255 characters. In reports I will simply combine these fields into one large text box so the user will see them as one large subject description. Memo fields are not an option because in the near future the database backend will be moved to SQL and I understand that SQL does not always play well with memo fields.
View 5 Replies
View Related
Aug 28, 2012
I'm trying to split out the first name from a GivenNames field which contains first and middle names. Some values in the GivenNames field have just a first name. I've tried the below formula, but still get some #Error responses where only a first name exists in the field.
My formula:
Given Name: IIf(IsError(Left([GivenNames],InStr([GivenNames]," ")-1)),[GivenNames],Left([GivenNames],InStr([GivenNames]," ")-1))
View 2 Replies
View Related
Jan 8, 2014
I have a client database that has null values in issue received, there are as many as ten issues per client. What I need to do is make a list of clients that have only two issues outstanding and a list of clients that have three or more issues outstanding. There are two tables that are connected correctly. The client table(one) and the issues table(many).
View 14 Replies
View Related
