Refernece Number Missing Digit During Form Transfer
Dec 6, 2006
i have a jobref (pk) as text at the moment because i tried it as Number data type and had a problem..
a reference number is made and when the date loses focus the refno is displayed in its textbox
this makes that refno
Private Sub cbojobdate_LostFocus()
Dim maxRef As Variant, maxID As Integer
Dim codeDate As String, maxDate As String
codeDate = Format(cbojobdate, "MMYY")
maxRef = DMax("jobref", "job", "jobref like '" & codeDate & "*'")
If (IsNull(maxRef)) Then 'test for new month
maxID = 0 'reset id to 0
Else
maxDate = Left(maxRef, 4) 'get date code
maxID = CInt(Right(maxRef, 4)) 'convert to int
End If
Me.cbojobref = codeDate & Format(maxID + 1, "0000")
End Sub
8 digits..
so a job on september 6th 2006 will have a ref no of 09060001
a job on february 1st 2006 will have ref no of 02060001
when i send the jobref to another form using this..
Private Sub cbojobfrom_AfterUpdate()
If Me.cbojobfrom = "t1" Then
Me.cbojobfrom = "LHR - T1"
End If
If Me.cbojobfrom = "t2" Then
Me.cbojobfrom = "LHR - T2"
End If
If Me.cbojobfrom = "t3" Then
Me.cbojobfrom = "LHR - T3"
End If
If Me.cbojobfrom = "t4" Then
Me.cbojobfrom = "LHR - T4"
End If
If Me.cbojobfrom = "h" Then
Me.cbojobfrom = "LHR"
End If
If Me.cbojobfrom = "ga" Then
Me.cbojobfrom = "Gatwick Airport"
End If
If Me.cbojobfrom = "gn" Then
Me.cbojobfrom = "Gatwick North"
End If
If Me.cbojobfrom = "gs" Then
Me.cbojobfrom = "Gatwick South"
End If
If Me.cbojobfrom = "st" Then
Me.cbojobfrom = "Stansted"
End If
If Me.cbojobfrom = "lc" Then
Me.cbojobfrom = "London City Airport"
End If
If Me.cbojobfrom = "lu" Then
Me.cbojobfrom = "Luton Airport"
End If
DoCmd.OpenForm "job_cash_inflight", , , "[jobref]='" & [jobref] & "'"
End Sub
and the job_cash_inflight form loads it with this..
Private Sub Form_Open(Cancel As Integer)
Me.[jobref].DefaultValue = Forms!job_cash_single![jobref]
End Sub
the problem is when i choose february the first for example
02060001.. when the reference number transfers to the incoming flight form.. it displays as 2060001
can anyone see why because before it transfers the refno.. the original job record has already been saved.. maybe when the id is being created it is not the best way.. or it doesnt match the text datatype.. if that is the case what number datatype shall i use in each of the tables jobref appears in?
hey guys i have the following line in my query for removing seconds from a time/date stamp and all works great except one thing if the time is 10:02 it is showing as 10:2 anyway of getting it showing correctly?
I can't display a 17 digit number in my table without losing the last couple of digits to 'rounding'. I've tried 'doubling' the field size but to no avail....whatever I attempt loses the last couple of digits to a 'nice round figure'.....
The numbers had initially been imported as text...which is really what they ought to be as they're identification numbers, but I had some issues using the find 'duplicate values' query and conjectured that was because the ID numbers had been defined as text---->though I could be wrong.
The VAL function works (to convert the text to numbers) but again -- I lose the detail of the last couple of digits.
I've been beating my head against this wall the entire day and at the very least, would like to know if what I'm attempting is viable. I've stumbled through function queries (with some success) and react like a deer in the headlights when it comes to VBA....
Hi. I have major report due within hours. I need to add a field to existing table that will be a 9 digit number, non duoplicating or - to take an existing field of 9 digits and allow it's values to change to random or incremental, non-duplicate numbers of 9 digits. I already have an autonumber field I must keep.
I have a datasheet with 7 columns. Two of the columns I'm working with are listed below.
File # Region # 2DE2-12345 2 3DE2-@@@ 3
In data sheet view I would like the Region # column to autofill based on the first digit of the file #. So, if file # entered = 3DE3-@@@@ then 3 would auto fill in Region Column. (data is entered in data sheet view)
I have used Left ([File #],1) to get value but cant make it auto populate the Region # column......
the phone rings.. the phone is connected to the computer.. the incoming caller's phone number is sent to the database.. and then possibly used in some way..(eg in a query or displayed on the screen in the database)
anyone had any experience in this process or can recommend a phone/cable setup url...
I have tried to search for an answer to this but no no avail! Please could someone help?
I have a start-up form, which has a combo box search feature which opens up a new form with the user defined record. As well as providing all present numbers users can type in their own number. The number it is searching is an AutoNumber. I tried to put in some error messages if the user selected a number greater than the maximum value using DMax
If cboSearch > intMax Then MsgBox "Not there", vbCritical + vbOKOnly, "Does not exist!" Exit Sub
This works really well. The problem I am having is that if entries have been deleted then there is a gap in the numbering, which in itself isn't a problem. But if the user enters a number not present then it just opens the correct form but with the first record. Is there a way of bringing up a message box informing the user that the value they have entered does not exist in the form they are searching?
I am working on a database where the records are numbered sequentially. On a regularly basis, records will be deleted leaving a gap in the record number sequence. I would like to have my db recognize that a number(s) is number in the sequence and use the missing number (filling in the gaps) when a new record(s) is added until all spaces are filling and then to continue from the largest number sequentially.
I have a series of data that start from 0 to 999. I have to save these data in text form in order to be able to update them to the form I need. I need these data to be in 3 digits vs number form. For instance, if the data is stored as 1, i want it to be updated it to 001. Is there any easy way to update the single digit and double digit numbers to be in the format mentioned above?
I have a database I am using to record financial transactions. I have a transaction edit/entry form that uses combo box lists to select the different segments of my 32 digit account numbers. The issue I am running into is that when I enter a value value in the first box/field the form jumps to the very first record.I then can use the navigation buttons to get back to the last record, and all the subsequent boxes/fields work fine without jumping to a different record.
I have a form with Driver details ie: Fields with Drivers Name, Payrole No,Vehicle Reg and Fleet No. When this form is filled in the form will be closed, the next forms in my db are vehicle defects which I want the vehicle reg and Fleet No: automaticly entered from the drivers detail form also a running sheet which I want the drivers name and fleet No: transfered to Automaticly. Is this possible?? Please Help
Im trying to make up a vb command to send date from a query based from to a table i have no clue how to even start.
The database is to record when a item is inspected so a user would type the date in to a unbound box and then at a click of a button the date along with the id code will transfer in to a table so that i can compair the difference between the dates of any one item
I have a main data entry form (frmpartquote). From this form I would like to open a new form (products) to create a sub set of records.
When I open the new form to add products to a main quote I would like to transfer the quotenumber on the quote form to the quotenumberfield in the products form.
I use the following code in the on open event in the product
Private Sub Form_Open(Cancel As Integer) If IsOpen("frmpartquote") Then partquotenumber.DefaultValue = Forms!FrmPartQuote!partquoteid End If End Sub
Now this works fine as long as the field is a number.
However I would like to use tartquotenumber as a text field
I'd like to ask you to help me in the following. The issue is basic I think, but not for me...
I'd like to transfer the data entered in the fields of a form into the fields of a table (that uses the same values). This table is connected with one to one relation to the table which the form bases on.
(An automatic data-transfer would be desirable. If it is difficult a button will do as well.)
Please write if you have got any good idea or solution.
I have a form that I want to copy selected field values to another form & subform. I have no problem copying some field values to frmEndorsement... but I can't figure out how to copy the other field values to the frmEndorsementSubform. I tried several ways but I'm still on a dead end...
Here's the actual example...
I want to transfer some selected fields from "frmEncounter" to "frmEndorsement" & "frmEndorsementSubform"
In my frmEncounter, I have these fields... mdsunit, patientID, lastname, firstname, pcp, & encountID ( EncountID is autonumber-PK)
In my frmEndorsement, I have... patientID, lastname, firstname
& in my frmEndorsementSubform, I have... pcp, msdunit, & encountID ( encountID is numeric-long integer )
There is a link master-child field between frmEndorsement & frmEndorsementSubform which is patientID.
and below is my code that I got from Ms. Candace Tripp... Thanks in advance... Jim
Private Sub cmdcopy_Click() On Error Resume Next Dim bOpen As Boolean Dim ctl As Control Dim frm2 As Form Dim ctl2 As Control
' check to see Endorsement Data Entry is open bOpen = IsOpen("frmEndorsement") If Not bOpen Then
' open form DoCmd.OpenForm "frmEndorsement" End If Set frm2 = Forms!frmEndorsement ' send data to frmEndorsement ' look at each control on frmlEncounter
For Each ctl In Me.Controls ' only look at combo boxes and text boxes If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
' now look at each control on frmEndorsement For Each ctl2 In frm2 If TypeOf ctl2 Is TextBox Or TypeOf ctl2 Is ComboBox Then ' if the control names are the same, ' set the value to that control on frmEndorsement If ctl.name = ctl2.name Then ctl2.Value = ctl.Value End If End If Next ctl2 End If Next ctl
Me.PCP = Forms!frmEndorsement!frmEndorsementSubform!PCP Me.EncountID = Forms!frmEndorsement!frmEndorsementSubform!Encount ID Me.mdsunit = Forms!frmEndorsement!frmEndorsementSubform!mdsunit
I have an on-going project and I am starting to have a problem with the DB. When open the form and search through the database I notice my total text box is not counting the options chosen. I have radio buttons on my form that are suppose to enter an amount of money into a text box, if the radio button is clicked, and I have total text box that I set up, in a query, to add all the amounts of the various text boxes with cost informtion in them. As I scroll through the records on some of the records these text boxes are not showing, or adding values for the options chosen. Why would that happen on only a couple of records and not the whole database?
i have a customer form and on the form i got a button to add equipment which opens another form, my question is how can i get the same company name from the customer form to be in the equipment form once i press the button?
i have done this so far
docmd.openform "EquipmentF",,,(not sure what to put here....i've tried "company = '" & company & "'" but this just seems to open the first company)
This im sure is an easyone to most of you but for me I cant find away around my problem!!
I have a form I use to enter infomation and I have also made a report that I am trying to get the infomation from the form onto the report.
I would like to have a command button on the form that once I click it, it then prints out the report. How do I get the information from a form to a report as when I have tried doing this myself it only allows me to use information from a query or table.
I've written an application in VB.Net and now I need to transfer the data on textboxes on a form to a report for printing. Only the data on the form is what I want to preview and print.
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this ID Username tblDailyLog TimeIn MorningBreakOut MorningBreakOut LunchOut LunchIn AfternoonOut AfternoonIn TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button 3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once) 3. The table is updated with the new time (using some dynamic SQL) 4. The table is repeatadly checked using a DO loop to make sure the the correct time went in. 5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens). 6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other) 7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs. I've built plenty of Databases in my time and have never come across this. __________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue) Dim TempSQL As String TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _ "#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _ "#" & Now & "#," & _ "'" & FieldUpdating & "'," & _ "'" & NewFieldValue & "'," & _ "'" & GetNTUser & "'," & _ "'" & TimesheetNumber & "'," & _ "'" & fOSMachineName & "')" ' MsgBox TempSQL DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False 'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table DoCmd.RunSQL TempSQL, False End Sub
Public Function CleanData(ByVal DataToClean As String) Dim TempData As String Dim i As Integer TempData = "" For i = 1 To Len(DataToClean) Select Case Mid(DataToClean, i, 1) Case "'" TempData = TempData & "`" Case """" TempData = TempData & "`" Case Else TempData = TempData & Mid(DataToClean, i, 1) End Select Next i CleanData = TempData End Function
