Modules & VBA :: Auto Insert Of Value
Oct 13, 2013
I want wage rate should be inserted automatically instead of manually in attendance sheet. In labourwage form, wage rate will be fixed for labour type with wagedate as and when required. Once I add attendate in attendform, it should look-up the table labourwage and auto insert wage rate entry in to wagerate column for the respective month ie from the given wage date up to next given wage date of labourwage. I have tried much, but not succeeded and presently entering manually which is time wasting. This should be possible with writing some code with VB.
View Replies
ADVERTISEMENT
Sep 1, 2005
Hi all!
I was wondering if you guys can help me out with this one:
I would like to have data auto-inserted from a particular colum in table 'x' in to a defined column in table 'y'. similary, I'd like to achieve auto-updation of this data as well as deletion. Can anyone please provide some guidelines?
A little more detail :
I have a column "Client ID" in the table client_personal_info, the rows of which I'd like to have updated in the "Client ID" column in the client_business_detail table.
So if say, ClientID_1 is a row that has been added by the user, I'd like to have it auto-inserted in the client ID column in the client_business_detail table
thanks in advance for your help
View 5 Replies
View Related
Feb 28, 2005
I have written a form from a table which holds employees details. This table has a photo field. The form has a control which displays the employee’s photo. It uses the field, photo as the control source.
This works okay but if you wish to enter a photo from the form you have to click on insert. Browse for the file etc.
The Sample database “Northwind” on the access help has a similar form, but under the photograph there are command buttons to add/change or remove the photo which is obviously a much more professional way of doing it.
I have looked at the properties of these control buttons and they seem to work through a visual basic program. As someone who has a very limited knowledge of VB is there a simpler way of doing this or would I be able to cut and paste the code to work on my form somehow (If Legal)
Any advice would be greatly appreciated.
View 6 Replies
View Related
Dec 1, 2007
when I am exporting in fixed width format (.txt file), is it possible to automatically include in the outputted text file:
a header of the current date and time, and
a footer that will say how many records are in a certain table?
edit: when i say header and footer, I mean the first and last entries in the text file
thanks
Venom
View 1 Replies
View Related
Nov 24, 2007
Hi All
I am new to this, hope you can help!!
I have 2 tables,
Customers and Routes
I want to auto insert data from Routes when I select the route Number from Customers,
the tables to auto update are
CruiseNumber:
CruiseName:
CruiseDate:
from
RouteNumber:
RouteName:
RouteDate:
I have read HELP in Access but I am a layman when it comes jargon. 'from the many to the few'??
Hope you can help....in 'Plain English'
View 6 Replies
View Related
Sep 28, 2006
how can i insert 'thisvalue' into 'thisfield' as soon as the form is opened??
View 5 Replies
View Related
Mar 15, 2005
Someone once posted an database on how to do this but I was not able to see how they were able to automatically input the data into a text field.
I know how to do a combo box to query and input those results into a combo box but I am confused how they got the information from the combo box selection to automatically put the other variables into the testbox. They did this without using the dlookup command. In fact it was done without any code.
They showed how to do this automatically without code and with two other methods (one being dlookup). Can someone please help me if it is clearcut as I don't have too much time to search the forums for empty answers (believe me I have already wasted too much time) Thank You
View 4 Replies
View Related
Feb 25, 2015
On my form have a status field with about 7 options to choose from a combo box, i have these values set in another table. What i would like is for my other field, which is a date, to automatically change whenever the status field is changed. So i know how long ago the status field was set. So basically if i change the status to complete i want the date to then set the date and time now.
View 5 Replies
View Related
May 22, 2014
Access 2010 keeps changing the Capitalization of objects when I don't want it to.
For example "Cancel" becomes "cancel". Later it will be changed back to "Cancel".
There is also a table with a field "FULLNAME". Several Queries build a field "FullName". As a result FullName, even in places like Application. CodeProject.FullName get changed to Application.CodeProject.FULLNAME.
(Track Name AutoCorrect is off. ) It is especially irritating because I export the code to text files and check it into source control. Sometimes dozens, or even hundreds, of files will show up as changed because Access did it's rename thing.
View 3 Replies
View Related
Dec 4, 2013
I have two subforms: sf1 (Based on Table 1) and SF2 (Based on Table 2) in the first I have the fields and CODE QTY.
I need to insert into the second table, as many records as QTY. to enter a serial number for each unit of the item. For example:
TABLE 1
COD. QTY.
001 2
006 1
I must insert into Table 2
001
001
006
Dim num As Integer, contador As Integer
contador = 0
num = Forms![FacturasRec_NSF]![FacturasRec_Det_SF].Form![CANLFR].Value
Do While contador = num
Insertar
contador = contador + 1
[Code] ....
but I could not even.
View 6 Replies
View Related
Nov 18, 2013
Been trying to get this piece of VBA code to work but not sure why it isn't.
This SELECT statement works fine:
Code:
strSQL = "SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
"].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"
However when I try to use this statement to create a new table like this:
Code:
strSQL = "INSERT INTO [" & strNewTable & "] SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
"].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"
db.Execute strSQL
I get the "Too few parameters. Expected 1." error.
Why would the syntax for the WHERE criteria not work when going from a SELECT to an INSERT INTO using the same SELECT statement that works independently as a row source?
View 5 Replies
View Related
Dec 9, 2014
I am reading through a table looking for duplicate values in the FullName text field. I want to store in a new table the duplicate records I find, storing just the MemberNumber and the FullName. When the VBA code runs and finds duplicates, the SQL statement to insert a new record into the Duplicates table asks for the value of LastMemberNumber and LastFullName when it already has the values and has displayed them in the message boxes! What am I doing wrong?
The code is:
Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim dbs As Database
Dim LastMemberNumber As Integer
Dim LastFullName As String
[code]....
View 7 Replies
View Related
Aug 8, 2013
after I insert a new record using INSERT INTO and then use DMax() (in a private sub) to look up the new record. About half the time DMax() pulls the new record based on the primary key field (AutoNumber) just fine. However, half the time it pulls the max record prior to the new record being inserted. I.e. record 1001 was added and DMax() pulls record 1000. I'm assuming that my issue has something to do with the timing of when the record is writen/saved in the table. Is there a simple method of refreshing the table that I can use prior to using DMax()?
View 2 Replies
View Related
Sep 6, 2013
How to Get latest id and inserted to another table like this image below
sdrv.ms/18HQ
See..i want in dbo_maid get id and insert to dbo_contract in maid_id
View 3 Replies
View Related
Jun 7, 2013
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...
View 6 Replies
View Related
Jul 10, 2013
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"
DoCmd.RunSQL SQLtext4
Note: My DLookup works fine by itself for other uses.
View 5 Replies
View Related
Mar 4, 2014
I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:
Code:
INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role )
SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role
FROM tblPeople
WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));
What I wrote for the VBA code is this:
Code:
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
[code]....
Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.
View 4 Replies
View Related
Jun 5, 2013
I currently have a bound form that adds a new student, however, I've decided I don't like bound forms as weird stuff can happen (like when the user exits in another way than I anticipated) so I'm making an unbound form which will add the student when a button is clicked. I already have this working on some other places so this should be no problem anymore.
However in my database a lot of student information is optional and only a first and last name are required (and ofcourse an autogenerated PK). So my question is how do I deal with these fields that might or might not be empty?
I could make a fairly big If ... ElseIf... construction where I slowly fill a String with all the optional fields but I was wondering if maybe there are some better ways to approach this?
View 12 Replies
View Related
Oct 4, 2014
I use the botton code for save image in SQL
Dim fDialog As Object
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim varFile As Variant
Set Cn = New ADODB.Connection
Cn.Open "Provider=SQLOLEDB;data Source=.;" & _
[Code] ....
But I have a problem when my database more than 2000 record. I think my problem in
rs.Open "Select * from Table_2", Cn, adOpenKeyset, adLockOptimistic
because select all record in Table_2
I decided to use bottom code
strSQL = "INSERT INTO Table_2(image_v,sabt,filetype_v,imageid) Values (mstream.Read,'" & [kodimage] & "','" & [file_type] & "','" & Me.Text8 & "');"
Cnxn.Execute strSQL
But I get an error with mstream.Read!!!!
View 3 Replies
View Related
Nov 11, 2014
I have a form that is calling data from a table. Then the user can determine if they approve the question or not. So what I am trying to do is take one combo box that has yes or no and write it to the database. I thought I had it correct, but appears I do not as it is not writing yes or no to the table. The field initially has no value and that is my query for calling the data for the user to select from. Here is the code that I have so far
<code>
'add approval to yes
CurrentDb.Execute "INSERT INTO travelerQA(approval)" & _
" VALUES('" & Me.cmbapproveda & "')"
</code>
View 5 Replies
View Related
Jun 11, 2013
Why I get a runtime 3134 error on this piece of code.
They are all text values
Code:
Dim strUserName As String
strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form.txtMacAddress
Dim strIDValue As String
strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1
Dim strAttribute As String
[Code] ....
View 6 Replies
View Related
May 31, 2015
I need to insert(append) first and last line in xml file...
So I have xml file and I need to insert one line to the top and at the of the text... Xml file contains Cyrillic characters and method to recreate xml file is going wrong with characters conversions... The last line a can append easy but the first line is a problem...
View 10 Replies
View Related
Jan 22, 2014
I am having a problem with below and getting a run-time error 3134
Code:
LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)
CurrentDb.Execute "INSERT INTO Model_types (Order) " _
& "VALUES (" & NewOrderNumber & ")"
The field 'Order' in Model_types is a Long Integer.
View 4 Replies
View Related
Jun 17, 2013
I'm trying to search through a table with serial card IDs, Order Numbers, and Ship Dates. I'm search through the Serial Card ID column and making my code work so that if the serial card ID exists and its ship date is between 9/30/2001 and 10/1/2011, then retrieve the associated order number and put in array. All of this works perfectly except for one tiny part : it's not returning a null for either unfound serial Card ID or serial card Ids that exist but their ship dates do not qualify. I would like to return a null value for these fields into the array as well as keep the found values in the array. The whole point of all of this is so I could count the number of rows for all the found order numbers and return a total value with that number.
View 14 Replies
View Related
May 29, 2014
I have an insert query that has been working just fine. I added three new columns (Paid, Balance, Invoiced) to both tables that I am using in my query. The query will insert the new records that don't exist in the one table just fine but it is not populating the new columns.
' Append records from aud_ck_reg (local table) to dbo_aud_ck_reg (table on server)
DoCmd.RunSQL "INSERT INTO dbo_aud_ck_reg ( audType, audDate, audUser, transid, customer_number_parent, transdate, create_1131, check_number, check_date, PW_voucher_date, " & _
"voucher_date, voucher_number, customer_number, credit, debit, fiscal_yr, month_number, calendar_month, calendar_year, calculated_cost, notes, CIT_number, Quarter, Hyperlink, Paid, Balance, Invoiced)" & _
"SELECT aud_ck_reg.audType, aud_ck_reg.audDate, aud_ck_reg.audUser, aud_ck_reg.transid,
[Code] .....
View 4 Replies
View Related
Mar 8, 2015
I am trying to formulate some code to run an SQL statement but it is not working.
The statement runs from a button on the sub form subOriginForm. Its intention is to collect the values from cboField1 and txtfield2 located on that form and insert them into tblTargetDB - but the whole thing isn't working.
Code:
Private Sub cmdMyButton_Click()
'DoCmd.SetWarnings False
INSERT INTO tblTargetDB ( field1, field2)
VALUES (forms!subOriginForm.cboField1, forms!subOriginform.txtfield2);
DoCmd.SetWarnings True
End Sub
View 4 Replies
View Related
