Modules & VBA :: Automate Process Of Uploading Inventory
Jan 3, 2014
I have a large database of items we sell on Amazon, I am looking to automate the process of uploading the inventory.
I am uploading a tab delimeted text file using the following code,
With CreateObject("msxml2.xmlhttp")
.Open "POST", strURL, False
.setRequestHeader "Host:", "mws.amazonservices.co.uk"
.setRequestHeader "User-Agent:", "VBA"
.setRequestHeader "Content-MD5:", md5hdr2
.setRequestHeader "Content-Type:", "text"
.send c2a
Forms!Form1.Text3.value = .responseText
End With
I am confident I have the signature and the MD5 Header, but I cannot get the data into Amazon!
I keep getting a non-descript error "InputDataError".
When debugging my Play API, I was told that the "send" command was not uploading the file contents, it was uploading the filename! So c2a is a string variable that contains the tab delimited data. This works like a charm for Play, but no joy for Amazon.
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
I am using Access 2013. I have the ability to pull a selection from a listbox. I can create a Select Sql string using that variable
sql As String, strCompany As String, strWhere As String strCompany = strCompany & Me.lstResource.Column(0, varItem) strWhere = "[Company name]=" & "'" & strCompany & "'" sql = "select * FROM tblResources WHERE " & strWhere
From here I have trouble. I see lots of examples to run an active query but not much on a select query. I have tried a number of things with no success. How to use this select statement to actually run against an existing access table? I am not putting it into a form or report at this time, just running the query to check results.
I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.
Code: Set rs = db.OpenRecordset("myTable", dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then rs.MoveFirst Do Until rs.EOF = True emplid = rs![Employee Number]
I use a macro (SendObject), which works, but it requires Us, or someone to go into Outlook to click on send.I'm new to vba, do I have to code something on outlook to send automatically?
what is the best way to import the data from the XML file into an access database table. The database I am working with has one large main table where all of the main record data is stored. There is a somewhat complex string of queries and reports based off this table that I am concerned about preserving. The problem is that the XML file is not structured in the same way the table is. The headings are named different, aren't in the same order, etc. I cannot use the import method and simply append it to the main table.After much searching around I have found two options:
1) Use the built in XML import method that access provides to create a secondary table. Then find a way to take data from individual fields in the second table and map and insert it into a new record in the main table.I already have the import part of this option working. The only part I can't seem to understand is how to take data from the second table and get it into a new record in the main table under the correct headings
2) Read the data from the XML file all at once and then map and insert it into the main table.I have not attempted this yet. I was having a hard time understanding how to retrieve the data from the XML file in the first place.
So.. which would be better/easiest to automate (most likely via button click on a form)? I only have a small understanding of VBA and even less understanding of anything XML.
I am trying to automate the generation of a reference number incrementally by 1.
In my main table (tblBooking) I have my primary key field autBookingID which is an autonumber.
In another table (tblBookingStops) I have the primary key as autBookingStopsID as the autonumber and then numBookingID linked to the above table (tblBooking). I have another field called txtGPSID so this is the number I would like to automate.
So for example:
tblBooking: autBookingID: 1234 tblBookingStops:
[Code]....
I want to put the code that would populate the txtGPSID on a form in datasheet view and would like to put the code on the load event.
The statement below is a snippet from a union query and is repeated 6 times within the SQL Statement for various reasons, I have to manually change this every month, again is there i tiny bit of code I can insert to replace the following so that it automatically runs the previous months data.
I have some code set up to automate scanning from access to a specific folder on the server. I have searched the web for different codes and have not come across anything that will scan an unknown amount of documents. The best I found was the code I am currently using, that will scan up to 10 documents separately, then convert these documents into 1 PDF. This is not ideal, however, because it would require the user to scan 1 document, wait, than scan another, wait, etc. Also, the code I am currently using will only scan from the glass, not the auto feeder, and I am unsure how to change this.
Private Sub cmdCOC_Click() 'scan COC On Error GoTo Err_Handler Const DEVNAME As String = "Brother MFC-7860DW LAN" Dim ComDialog As WIA.CommonDialog
I've been using Access 2007 to run queries on a database where we eventually export results as separate Excel spreadsheets for individual clients. The process is quite involved, using queries to change fields from code letters to words and splitting the database up into different client tables, saving the tables under date order and with different client codes.
I now need to pass this role on to colleagues, so need to make everything as straightforward as possible.
I had thought to use a Form as the user interface, with a minimum number of buttons, however I need either the system or the user to amend the date for the initial table, then to use this new table and run a series of standard queries on it, then produce the separate tables.
I don't think I can just use macros behind the buttons, because the database name is changing each time.
I assume some parts will be too tricky to automate - it will be necessary for colleagues to follow instructions instead.....
I have an inventory app, how to make daily opening stock /closing stock.I want the system done automatically,that is as users exit,closing stocks & next day opening stocks as users log in.Then daily stock reports can be generated.
I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. how to find an item number in target table by looping through the source table?
Here's the code I've written which doesn't work.
Dim I As Integer Dim db As Database Dim rs As Recordset Dim rs2 As Recordset Set rs = Nothing Set rs2 = Nothing Set db = CurrentDb
I am setting up a inventory database and i will like the cost of the items to be calculated using FIFO.
Sample tables:
PARTS TABLE: part code description cost quantity on hand
PART TYPES part type id party type
INVENTORY invent id location reorder quantity
INVOICE id date part code part type qty cost ext cost
NB
For every item i will like the previous cost to be charged before the new cost. eg. if 10 pens where entered at $2 and another 5pens were entered at $4 each and 1 need 11 pens, i want the first 10 to be charged at $2 each and one at $4.
I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].
Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.
I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.
Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.
If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.
Here's the associated code
Option Compare Database Option Explicit Private Sub cmdAddRecords_Click() Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control
Code that will allow me to post selective data from a purchase received form to the inventory using a command button. this command should also add to the existing quantity in the inventory.
I have created my database - I now need to upload data from my old database.I have the old data in an excel spreadsheet and have edited the data so that it matches the coloums in the new database.I have one problem,I can upload all the person data into the person tableand I can upload all the organisation data in the organisation table(I'm assuming I just copy and paste it into the tables in the new database.)BUT and this is a big BUT.. the employment table is the linking table where you select an organisation from the drop downlist and you select a person from the person dropdown list, you add other details about emplyment and save the record.Does this mean I will have to do this manually, i.e create all the links manually??And is my cut and paste method above corect??Thanks is advanceEdit: I have attached the database to help you understand
You must have creted databases before which needed to be populated with dada from another source.I have a database with three tables.PeopleOrganisationsEmploymentI have two spreadsheets - one with organisations and one with people.Approximately 700 records for each - how will I upload these and more importantly will I have to manually make the links in the linking table(emplyment??)Please adviseThe database is attached to help
I can't seem to upload on this site anymore (I could do it a few days ago). Is there a problem with the uploading or am I doing something wrong? It looks like my uploading capabilities are off. Can I please have uploading capabilities?
I have some sample code I'd like to upload to answer a few threads.
I want to push a table from Access to a specific SQL Server database.
Is there an easy way to do this? I have an ODBC connection to the SQL Server Database and I can import tables from SS to Access... but is there a way to do the reverse?
I have a problem and wander if anyone knows a solution?
I have a central database that runs on a stand alone machine (soon to run on a server with VPN connection), it has a form that enables us to report faults this works fine however i would like to be able to send the form out to our operators (all over the world) for them to fill in when needed. then what we would like is for them to send it back to usand then for us to upload it to the central database.
Hi there im trying to build a database that stores a large number of photos and would like some suggestions on how i can upload multiple photos (20+) at one time without uploading each one individualy, preferably through a form.
To outline the problem, each member of the team has a camera that they take to jobs, when they return i want them to be able to open a new form, enter the job number (the primary key) onto the form and then somehow drag and drop the pictures from the camera onto the form to upload them.
Is this possible through Access? is this the best way of uploading multiple photos easily? and how do i build this?
I am currently working on a project for work on re-creating our database for our daily audits. We enter daily information on a form which feeds to the table and in return we run the queries to get the percentages and all that good information. I was asked to be able to load a picture of to a particular record. Can I do that from the form and that link to the table and to the queries that run?
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1 1 2 1 3
2 1 2 2 2 3 2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
I built a form that will allow each page header to take the info needed depending on who is using the data base. I seem to be having a problem having the Logo uploaded so that it can be used. Since I am new to Access, my only example would be like a web page. use the location of the picture in the code and it pulls it up for each page associated. I guess this would be 2 questions. How do I add to my form the upload of the graphics and how do I get the reports and forms to pull that picture into the header every time.
