Identity help Results

Hello all! I have 2 problems with my db program. I have a form for each problem. They're not on the same form.

1. I have a report that is serving as a Late Notice to my tenants. It has a Previous balance field, rent field, late fee field, and total due field. For some reason when I add the late fee to the tenants I select in the form, the report doesn't reflect the same values as the form does.

2. I'm using query's to calculate a new Amount Outstanding when I add court costs for those tenants I select in my form. When I select the tenant and hit Add Court Costs, it asks for a parameter from my query but I can't figure out why its asking me this because I made it almost identical to the Late Fee querys and it works.

My db is pretty big so I have it up on http://briefcase.yahoo.com. Username: davidinncsu2003 Pass: ncsuwon. Pick the mdb file with the latest date on it.

If somebody could take a look and show me how to fix this, I'd appreciate it.

Thanks!
David Somers

Hey all, I need some assistance in creating a report in access 2003. The difficulty is that the report needs to select data from a table by checking three different columns that contain non-identical text values (22 variables to choose from). In other words, if the text value in columns 1, 2 or 3 equals X then select data for the report. I need to list the data by category (value from columns 1,2 or 3), with each category listed on a separate page. Creating an expression that will perform such a feat is eluding me. I have searched on line for expression examples that might help without success. Can anyone offer a solution or point me toward the necessary information?

Hi folks

I run and Access database that contains performance information at work. Each day a report is run which has a depot name and a performance figure and this gets sent out to all 30 depots as an email attachment using the "Send Object" macro function.

I've been asked to regionalise the reports instead of just sending them out in depot alphabetical order. There are 3 regions.

I've got all 3 individual reports built but I dont want to send 3 emails where I used to send just 1. I'd ideally like to have a single report that combines each of the reports I've already built, or alternatively have 1 email that sends 3 reports. Each of the regional reports I've built is identical in layout, it just takes data from a separete query.

I've been playing round with the grouping function but cant see a way I can achieve this.

Any help would be appreciated - Please note that I'm far from an expert so if you're getting into VBA I'll need lots of hand-holding

Thanks in advance
Stu

does anyone know if there is an issue with using @@identity more than once in the same procedure? if so, is there a workaround? i am trying to insert information in one table, then get the autonumber value for use in a second table, then get the autonumber from the second table for use in other tables. getting the first autonumber works just fine, but the second one always gives me a key violation error and the variable that stores the second autonumber is showing a value of 0.
any help is appreciated!

I'm using vbscript which is almost identical to VBA. I did the hard part, I read in a textfile, parsed it, and inserted the words and numbers into Excel.

But it's the "easy" part that's killing me. One of the columns which I am parsing has spaces prepended, such as

" John Smith"

I can't get rid the spaces. I've tried

Name = RTrim(Name)
name = Ltrim(Name)
name = Trim(Name)

I've even tried looping through the chars

str = ""
For i = 1 to Len(word)
C = Mid(word, i,1)
if C " " Then str = str & C
next

it's not working. They "look" like spaces but vb won't recognize them as spaces and so won't trim them. Why?

I'm trying to get this project on the floor NOW. Help !!!

I am trying to insert into three SQL Server tables. The first insert will go into the Audit table so it can generate an Audit_ID. Then using that Audit_ID, I will run a For loop to insert (Audit_ID, Auditor) into the Auditor junction table and (Audit_ID, Auditee) into the Auditee junction table. I believe the For loop is necessary because the Auditors and Auditees values come from a listbox.

I have some VBA code below but I am now stuck. I get a syntax error

So my question is: how can I retrieve the @@Identity (Audit_ID) and use it in a For loop?

Thank you all for your help! If I figure this out, this will become a turning point for my work project and make my boss happy!!


	Code:
	On Error GoTo Err_test
    Dim strSQL As String
    Dim Group As String
    Dim Year As Integer
    Dim Quarter As Integer
    Dim i As Variant
    Dim j As Variant
    Dim lsql As String
    Dim Auditor as String
    Dim Auditee as String
    Group = [Forms]![frmAddAudittoPlan].[cboGroup].[Value]
    Year = Me.txtYear
    Quarter = Me.cboQuarter.Value
    strSQL = "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear) Values ('" & Group & "', " & Quarter &
", " & Year & ");"
    CurrentDb.Execute strSQL, dbFailOnError
 
    For Each i In Me!lstAuditors.ItemsSelected
        Auditor = Me!lstAuditors.ItemData(i)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ("SELECT @@Identity", '" & Auditor & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next i
 
    For Each j In Me!lstAuditee.ItemsSelected
        Auditor = Me!lstAuditee.ItemData(j)
        lsql = "INSERT INTO dbo_jntbl_AuditToAuditor (Audit_ID, Auditor_ID) Values ("SELECT @@Identity", '" & Auditee & "')
        CurrentDb.Execute lsql , dbFailOnError
        Next j
 
    MsgBox ("Audit Plan Added")
    End If
Exit Sub
Err_test:
        MsgBox "Error: " & Err.Description
End Sub



Hi there,

This could be a easy one for a VBA "savvy"..
I have the following piece of validation code(part of a function) that returns True/False based on whether the required data is filled:

For Each ctl In Controls
If IsNull(ctl) And (ctl.Tag = "*") Then
ValidateRequiredFields = False
ctl.SetFocus 'Set focus to the control
Me.btnMissingData.Visible = True
Me.imgYellowbck.Visible = True
Exit Function
Else
ValidateRequiredFields = True
Me.btnMissingData.Visible = False
Me.imgYellowbck.Visible = False
End If
Next ctl

Now my issue is, I have added five new fields that are also mandatory. However, each one of them is mandatory, depending on the identity of the user or which group they belong to.(The db captures the windows log in and looks up the group for the user).

So only one of these 5 fields is mandatory at a time... For example if usergroup = HR then DateHR is mandatory, etc...

How can incorporate this nicely in my piece of code above without adding long messy code just for the 5 new fields..

I would really appreciate any help..
Thanks,
Elias.

What is the most efficient way of copying an entire record (or set of records) between two identical tables in different databases.
I would like to create an update button, which does this transfer.
Moreover, I would like it to transfer the records which were modified aswell.
Im a newbee to access vba, so i appreciate any help in this regard. If you can provide me a sample, that will be very nice.

Thanx

Hi,

Not a good start to my day. I created a copy of my live Database and created a button on my form that sets up an Outlook appointment. This works fine. I then copied the form with the button and pasted it into the live version. I also copied the Module that defines Outlook. Word for word this is identical. However the code now displays an error saying 'user not defined.' on Dim'ing the Outlook.Application. Does anyone know why this now doesn't work on the live version, but still works in the copy?

Any advice would be very much appreciated.

Hi,

After upgrading the Access backend to a SQL Server backend, running an upload of the data from Access to SQL Server, 2 tables of the total of 72 tables reject the load because of an IDENTITY column and stay empty. Comparing with other tables, I cannot find any reason why this 2 tables were upgraded needing the IDENTITY column.
I am new to SQL, so anybody could give me a tip what could be the reason for this, so I can bypass it with some other solution?

Thank you for any help.

In access I had a table with a primary key set to auto update and uni records. When you would enter a new record this field would update with a new number. In SQL I have the same primary key the data type is set to var and I have the identity set to yes and increments to 1. When I start a new record it does not auto update this field with a new number?
Why?

jon

Hi Friends,
I am having one query regarding Identity in SQL.
I want to Give ID column the permission to get auto incremented with the data inserted in the table.
Can anyone help?

I need help with the Rand() function in TSQL.

I needed to return a random 20% of the rows from a certain table.
On the surface it seemed that I could use the identity column for the seed
and check for Rand() between 0.1 and 0.2.

However, Rand(id) returns a very SEQUENTIALLY NUMBERED set of rows. This
results in selecting data from a few horizontal "bands" in the table.
The data was anything but random.

In the Help files, they show using GetDate() as a seed, but admit that
in a query all of the Rand() return values are the same!

I ended up using > --> SubString(Cast(Rand(id) As Varchar(20)), 7, 1) In ('1', '2')
The 7th character seemed to be "somewhat" random.

This did end up generating a very nice looking sample. Not perfect in the
ideal sense, but adaquate for this task.

This is very easily handled in Access, using a Public Function call in
a query that does the Randomize, etc.

Is there a "real" solution in SQL Server?

Thanks,
Wayne

I need to build a database from scratch. Without hiding anything, i am a teacher teaching databases to students. I have been given some pre release materials containg a scenario. I have an ok level of access however i have problems interprating scenarios into databases. Please don't get me wrong i don't want anyone to do the work for me but Would anyone be able to assist me in doing the following:

Quote:
The company wishes to use its website to advertise and to take bookings for the “Deer Valley Experience”. A database is required which allows users of the website to book seats on these special trains. You have been asked to produce this database. In total there will be six special trains, three on each day. The trains will leave from Linfield station at 09:00, 11:30 and 13:00. Each train will be made up of an engine and four identical 32-seater carriages labelled A, B, C and D. Each carriage has eight sections, four on either side of an aisle. Each section
contains two pairs of seats, one pair facing forwards and one pair facing back. Each pair of seats has a window seat and an aisle seat. Individual seats will have a code which consists of the carriage label Quote:
(A, B, C or D) followed by a two digit number.


The customer must enter at least their last name, the first line of their address and their post code. There will be the option on this form to confirm or cancel the booking. If the cancel button is pressed then nothing is stored. If the confirm button is pressed then the customer is assigned a unique identification (Customer ID), which consists of the first three characters of their last name followed by a four digit number. The customer’s data is then saved and the seat is reserved by linking their identification to the relevant seat booking and the system is updated. If a seat is still available then it will contain the word“Free” instead of a Customer ID.


So far the tables i have are as follows:
tbl_Trip (Trip_no, date, time)
tbl_Seats (Seat_no, forward/backward, window/aisle)
tbl_Customer (Customer_no, fname, sname, address1.......)
tbl_Booking (Booking_Ref_No, Trip_no, Seat_no, custno, Date_Booking_Made)

I would really appreciate peoples help on this.

Regards

Hey there,

Please could somebody point me in the right direction?

I would like to add multiple identical records to a table via one input form. All the details to be added to the table will be identical with the exception of the primary key, which is an autonumber field. It will usually be 10 records at a time.

The background for this is.... Each manager has a batch of 10 overtime slots. He gets 10 unique approval numbers generated by the database before the overtime is completed. At this stage the records can contain all the known information which is identical like the manager’s name and area. Once the overtime has been done the record is updated with the information which was not known at the time the unique number was generated, like type of work and time taken.

My apologies if this is a dumb question or incredibly obvious. Any help you could offer would be appreciated!!

Thanks
Fiona

can any help me merge two identical database? thanks.

My company has been compromised, to say the least, by a trusted employee of 18 years. There are emails on this Forum with the appearance that she is associated with Amerifax. She is not. The only person from Amerifax that will be using this site is Bob Snow, which is me.

Since my identity has been compromised, to say the least, it's important to me to eliminate any misunderstandings that could occur. Especially since I value of this website very highly

Furthermore, any name, associations between Heather and Amerifax is to be considered incorrect.

Thank you.

Bob

I would like to add a button to a form so that when clicked all the data in that record is moved (cut and paste) to another identical table, like archive.
I want the action to act as a delete, however instead off deleting i would prefer it to be stored in another backup table. a safty net. Any ideas ?????????

In Access 2010, Win 7. I have 2 tables. Very simple.

1) tblEmployee Fields: EmployeeID (KEY) , FirstName, LastName, Site, UnitNbr. That's stagnant data, as it rarely changes. But there maybe several hundred employees. The only thing that might change is the "Site" and "UnitNbr".

2) tblLogBook This is a daily record of the hours applied per day in 4 categories per employee. However this record needs the EmployeeID to identify who the record belongs to. So I have a "Foreign Key" identical to the "Key" in the tblEmployee. The KEY is a AutoNumber.

So each record needs to show 1) Unique "Auto Number" (Key); 2) who the EmployeeID; the date; Hours 1, 2, 3, & 4; UnitNbr; & Site.

So what I'd like to do (head banger) is design my data entry form to populate the tblLogbook.

When I open it, I start with a Date pick. Then I'd like to type in the "EmployeeID" and have it AUTO populate the "UnitNbr", and the "Site" assigned to him.

My relationship currently is from the tblEmployee (EmployeeID) [Key]; to the tblLogBook (EmployeeID) foreign key.

I've spent days trying to build this form. It's been a real

Hiya, I'm making a library system as a school project.I have two tables, called books and publishers.There is a column of data on the book table called 'publisher', which has identical data to a column on the publishers table called 'names'.These 2 entities are linked
I also have forms of these tables.On the 'item' form I want the user to be able to click on a button next to the 'publisher' entity and be presented with that publisher's details on the Publisher form.
I don't know if this is a query or what
Pleeease help xxx


Not finding an answer? Try a Google search.