Excel to Access - code issue HELP!! - Microsoft Access / VBA

AR Hall

This code goes through 3 work sheets in the same XL workbook, day, eve and
night shift
in each sheet their are columns representing production work orders and
reject and downtime for each order
so in the tblproductionde tail there is one record for each work order and
many reject or downtime records (one to many)
code works fine to get a new record for each work order main info but when
creating the sub table related records for rejcts and downtime it writes the
same autonumber over and over again.

Public Sub dBSAVE()
DayShift
EveningShift
NightShift
End Sub

Sub NightShift()

Dim db As Database
Dim rs As Recordset
Sheets("Night Shift Report").Select
Dim col As Integer
Dim row As Integer
Dim inc As Integer
Dim prodcode As Long
Set db = OpenDatabase("S :\Production Database\Produc tionData_tables .MDB")

For col = 2 To 17 Step 3
Sheets("Night Shift Report").Select

If Worksheets("Nig ht Shift Report").Cells( 10, col).Value = "" Then Exit
For

Set rs = db.OpenRecordse t("tblProductio nRunDetail")
Dim NightId As Long
With rs
.AddNew

rs(2) = ActiveSheet.Ran ge("k3").Value 'date
rs(3) = ActiveSheet.Ran ge("k4").Value 'supervisor
rs(4) = ActiveSheet.Ran ge("h3").Value ' shift
rs(5) = ActiveSheet.Ran ge("h4").Value ' time
rs(6) = Worksheets("Nig ht Shift Report").Cells( 10, col).Value
'product
rs(7) = Worksheets("Nig ht Shift Report").Cells( 14, col).Value ' good
rs(8) = Worksheets("Nig ht Shift Report").Cells( 64, col).Value ' bad
rs(9) = Worksheets("Nig ht Shift Report").Cells( 24, col).Value '
dryWt
rs(10) = Worksheets("Nig ht Shift Report").Cells( 23, col).Value '
wetwt
rs(11) = Worksheets("Nig ht Shift Report").Cells( 27, col).Value '
wttest
rs(12) = Worksheets("Nig ht Shift Report").Cells( 31, col).Value '
grosshours

.Update

End With
rs.MoveLast ' to get the autonumber field
NightId = rs(0)
rs.Close

'Rejects-------------------------------------------------------------

Set db = OpenDatabase("S :\Production
Database\Produc tionData_tables .MDB")
Set rs = db.OpenRecordse t("tblProductio nRunRejects")
For row = 44 To 63 Step 1
If Worksheets(strS heet).Cells(row , col).Value = "" Then Exit For

rs.AddNew
rs(1) = NightId
rs(2) = Worksheets("Nig ht Shift Report").Cells( row, col).Value
rs(3) = Worksheets("Nig ht Shift Report").Cells( row, col + 1).Value
rs.Update
Next row
rs.Close

' Downtime-----------------------------------------------------------
Set db = OpenDatabase("S :\Production
Database\Produc tionData_tables .MDB")
Set rs = db.OpenRecordse t("tblProductio nRunDownTime")
For row = 37 To 39 Step 1
If Worksheets("Nig ht Shift Report").Cells( row, col).Value = "" Then
Exit For
rs.AddNew
rs(1) = NightId
rs(2) = Worksheets("Nig ht Shift Report").Cells( row, col).Value
rs(3) = Worksheets("Nig ht Shift Report").Cells( row, col + 1).Value
rs.Update
Next row
rs.Close

Next col
End Sub

Public Sub DayShift()

Dim db As Database
Dim rs As Recordset
Sheets("Day Shift Report").Select

Dim col As Integer
Dim row As Integer
Dim inc As Integer
Set db = OpenDatabase("S :\Production
Database\Produc tionData_tables .MDB")

For col = 2 To 17 Step 3

Dim DayID As Long

If Worksheets("Day Shift Report").Cells( 10, col).Value = "" Then Exit
For
Set rs = db.OpenRecordse t("tblProductio nRunDetail")

With rs
.AddNew

rs(2) = ActiveSheet.Ran ge("k3").Value 'date
rs(3) = ActiveSheet.Ran ge("k4").Value 'supervisor
rs(4) = ActiveSheet.Ran ge("h3").Value ' shift
rs(5) = ActiveSheet.Ran ge("h4").Value ' time
rs(6) = Worksheets("Day Shift Report").Cells( 10, col).Value 'product
rs(7) = Worksheets("Day Shift Report").Cells( 14, col).Value ' good
rs(8) = Worksheets("Day Shift Report").Cells( 64, col).Value ' bad
rs(9) = Worksheets("Day Shift Report").Cells( 24, col).Value ' dryWt
rs(10) = Worksheets("Day Shift Report").Cells( 23, col).Value ' wetwt
rs(11) = Worksheets("Day Shift Report").Cells( 27, col).Value '
wttest
rs(12) = Worksheets("Day Shift Report").Cells( 31, col).Value '
grosshours

.Update
End With
rs.MoveFirst
rs.MoveLast
DayID = rs(0)
Debug.Print "DAY"; DayID; "RC"; rs.RecordCount

rs.Close

'Rejects-------------------------------------------------------------
Set rs = db.OpenRecordse t("tblProductio nRunRejects")
For row = 44 To 63 Step 1
If Worksheets("Day Shift Report").Cells( row, col).Value = "" Then
Exit For
rs.AddNew
rs(1) = DayID
rs(2) = Worksheets("Day Shift Report").Cells( row, col).Value
rs(3) = Worksheets("Day Shift Report").Cells( row, col + 1).Value
rs.Update
Next row
rs.Close

' Downtime-----------------------------------------------------------
Set db = OpenDatabase("S :\Production
Database\Produc tionData_tables .MDB")
Set rs = db.OpenRecordse t("tblProductio nRunDownTime")
For row = 37 To 39 Step 1
If Worksheets("Day Shift Report").Cells( row, col).Value = "" Then Exit
For
rs.AddNew
rs(1) = DayID
rs(2) = Worksheets("Day Shift Report").Cells( row, col).Value
rs(3) = Worksheets("Day Shift Report").Cells( row, col + 1).Value
rs.Update
Next row
rs.Close
Next col
End Sub

Public Sub EveningShift()
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("S :\Production Database\Produc tionData_tables .MDB")

Dim strSheet As String

Sheets("Evening Shift Report").Select
strSheet = "Evening Shift Report"
Dim col As Integer
Dim row As Integer
Dim inc As Integer

For col = 2 To 17 Step 3

If Worksheets("Eve ning Shift Report").Cells( 10, col).Value = "" Then
Exit For
Set rs = db.OpenRecordse t("tblProductio nRunDetail")

Dim Eveningid As Long

With rs
.AddNew

rs(2) = ActiveSheet.Ran ge("k3").Value 'date
rs(3) = ActiveSheet.Ran ge("k4").Value 'supervisor
rs(4) = ActiveSheet.Ran ge("h3").Value ' shift
rs(5) = ActiveSheet.Ran ge("h4").Value ' time
rs(6) = Worksheets("Eve ning Shift Report").Cells( 10, col).Value
'product
rs(7) = Worksheets("Eve ning Shift Report").Cells( 14, col).Value '
good
rs(8) = Worksheets("Eve ning Shift Report").Cells( 64, col).Value '
bad
rs(9) = Worksheets("Eve ning Shift Report").Cells( 24, col).Value '
dryWt
rs(10) = Worksheets("Eve ning Shift Report").Cells( 23, col).Value '
wetwt
rs(11) = Worksheets("Eve ning Shift Report").Cells( 27, col).Value '
wttest
rs(12) = Worksheets("Eve ning Shift Report").Cells( 31, col).Value '
grosshours
.Update

End With
rs.MoveFirst
rs.MoveLast
Eveningid = rs(0)
Debug.Print "EVEn"; Eveningid; "RC"; rs.RecordCount
rs.Close

'Rejects-------------------------------------------------------------

Set db = OpenDatabase("S :\Production
Database\Produc tionData_tables .MDB")
Set rs = db.OpenRecordse t("tblProductio nRunRejects")
For row = 44 To 63 Step 1
If Worksheets("Eve ning Shift Report").Cells( row, col).Value = "" Then
Exit For
rs.AddNew
rs(1) = Eveningid
rs(2) = Worksheets("Eve ning Shift Report").Cells( row, col).Value
rs(3) = Worksheets("Eve ning Shift Report").Cells( row, col + 1).Value
rs.Update
Next row
rs.Close

' Downtime-----------------------------------------------------------
Set db = OpenDatabase("S :\Production
Database\Produc tionData_tables .MDB")
Set rs = db.OpenRecordse t("tblProductio nRunDownTime")
For row = 37 To 39 Step 1
If Worksheets("Eve ning Shift Report").Cells( row, col).Value = "" Then
Exit For
rs.AddNew
rs(1) = Eveningid
rs(2) = Worksheets("Eve ning Shift Report").Cells( row, col).Value
rs(3) = Worksheets("Eve ning Shift Report").Cells( row, col + 1).Value
rs.Update
Next row
rs.Close
Next col
End Sub

Nov 13 '05 #1

Subscribe Reply

2844

Similar topics

1502

working with excell from access code

by: Rocky A | last post by:

I need to open an excel workbook and import info to my access program. That isn't the problem, I've got the code down for doing what I want to do. I'm declaring the variable and setting it like this. Dim objEx As Object Set objEx = CreateObject("Excel.Application") My problem is after I'm done and exit the access code module and I shut the excel file down like this

Microsoft Access / VBA

5727

Switching between Access and Excel using code

by: garry.oxnard | last post by:

Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access database - containing a list of addresses. Sequence of events is = (1) Excel template opens in its default XXX.xls filename. (2) Code runs to save the spreadsheet as XXX.xls. (3) User clicks a button to open an Access database containing an

Microsoft Access / VBA

8765

Reading, writing and deleting records in access database, PLease help

by: vadarv | last post by:

Hia! I'm a total amateur to VBS but need help on a VBS script. This is used in a HMI system from Siemens called WinCC, used for process viewing and control. What I need to is to write to a table in Access, then read these values and then delete records (rows) in Access. By using help functions in WinCC I have this set up: I created an Access database with the WINCC_DATA table and columns (ID, TagValue) with the ID as the Auto Value. ...

Microsoft Access / VBA

6031

Excel-Access link

by: farhaaad | last post by:

Hi everybody, I just wanted to know if i can make a form in excel (the same as access forms), so when i enter data in excel form it goes to a table in access ? I mean when i enter a value in a cell linked to access table, it goes to table and when i enter another value in the same cell the value goes to the next record. I thank you people in advance!

Microsoft Access / VBA

1358

Having a problem with the following code...Please help!

by: eskelies | last post by:

Below you will find the code that I have written. I am asking it to search for an Excel file. Once it finds the correct dated Excel file it opens it. From there it is in search of information. I can't determine if the macro is working without fixing the following date - 1 issue. Any assistance would be greatly appreciated. I am using Excel 2003. Dim FileDate, Cusip, Here, Today, There FileDate = InputBox("Enter File Date in yyyymmdd...

Visual Basic 4 / 5 / 6

3931

Excel VBA - ERROR TRAPPING HELP!!!

by: brenty66 | last post by:

I have a userform used as an interface to store information on excel spreadsheets. I have put a decent amount of work into it to this point and all of a sudden I am getting a catastrophic error message. It pops up every now and then when I try loading the userform. When I select OK it will then show a runtime Error 361 "Can't load or unload object" At this point it will usually close out of excel and ask to send an error report. Sometimes I...

Visual Basic 4 / 5 / 6

2461

Excel control click issue need more than 11 values

by: MyWaterloo | last post by:

Hey all, I don't know where to go with this Excel question so I thought I would come here. You all have always been able to answer/help me with all my Access questions. I know this is not exactly kosher asking this in an Access forum... I am trying to create a chart in Excel 07, I am pulling data from multiple sheets (each sheet being a year). Within each sheet is 12 different months worth of data for the corresponding year that each...

Microsoft Access / VBA

1247

how I send a e-mail (with attachment) through MS Access code ??

by: BhupendraNVyas | last post by:

Dear All, I want to send Out standing E-Mail detail to customer's throug ms access code .....can you help me for genrate a mail and attach a file from local hard disk drive. Regards, Bhupendra

Visual Basic 4 / 5 / 6

8149

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8097

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8240

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8411

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6072

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5527

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4042

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2546

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1692

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP