Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 22nd, 2014, 02:22 PM
Registered User
 
Join Date: Oct 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need Help with creating and Updating table

I have a functions that adds a sequence number to a table, what i need to do that I do not have the expertize is based on the FT7CPT look up another table and create a record depending on what type of person it is. If it is an medx than I need to create a record with a pro fee and facility fee. if it is not than i need to create a record with a global fee. In each field is the xref to the item i need. so i need to replace the FT7CPT with the item number and create a new row. here is the code i used to sequence the items in the table.

Public Function SetChargeItem()

Dim db As Database
Dim rs As DAO.Recordset

Dim currentCode As String
Dim seq As Long


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tmp_FT_seg ORDER BY TBL_DAILY_BILLING_NAME, Patno, TBL_DAILY_BILLING_FSEQ, SEQUENCE, FT7CPT")

currentCode = ""

With rs

Do While Not .EOF

If currentCode <> !PatNo Then
currentCode = !PatNo
seq = 1
Else
seq = seq + 1
End If

' Based on FT7CPT code need to lookup the value in another table called TBL_INV_IMPORT
' tbl_inv_import has four columns CPT Code, Profield, Facilityfield, and global field.
' Need to return the value in one of three fields into FT7cpt depending on the following
' if medx than need to create one record with number in pro field
' if medx need to create another record with number in facility field
' if not medx than need to create one record with global field
' loop through each tmp_ft_seq record performing this routine..



.Edit
!sequence = seq
.Update
.MoveNext
Loop

End With

Set rs = Nothing
Set db = Nothing

End Function





Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating one table with data from another table dirtdog22 Access VBA 1 January 21st, 2008 04:41 PM
Updating table every now and then rtr1900 Classic ASP Databases 2 December 2nd, 2005 03:12 AM
Updating table Hudson40 Access VBA 6 March 23rd, 2005 12:07 PM
updating table markhardiman Classic ASP Databases 2 September 29th, 2004 12:35 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.