Help with append query (1 Viewer)

[iain1978]

Hi,

Any assistance someone can give me with this append querry issue is appreciated.

I have a table called tblRoleAssignments that has three fields RoleAssignmentID (PK), RoleprofileID & ApplicationrightID. It looks something like this.

RoleAssignmentID, RoleprofileID, ApplicationrightID
58, 12, 317
59, 12, 796
60, 12, 1
61, 13, 179
62, 13, 84

my append query will write new records depending on the RoleprofileID's I entered, for example If I run my append query on the above list I get the following output

RoleAssignmentID, RoleprofileID, ApplicationrightID
63, 0, 317
64, 0, 796
65, 0, 1
66, 0, 179
67, 0, 84

My problem is when I run my append query I want it to enter a specific value in the role profile ID column (taken ideally from a field on a form that will be loaded) so instead of the query entering a zero it writes to the table a vaule I want. Below is how I want my append querry to write the data if I wanted the RoleprofileID to be set to 35.

RoleAssignmentID RoleprofileID ApplicationrightID
63, 35, 317
64, 35, 796
65, 35, 1
66, 35, 179
67, 35, 84

Any ideas????????

 

[ThreeCrow]

The "35" I take it is something that you have entered into a textBox on a Form.

You have to tell (so to speak) Append Query where the Field is.

Somthing Like

RoleAssignmentID, RoleprofileID (put this in the Criteria [Forms]![YourFormName].[RoleprofileID], ApplicationrightI D

 

[iain1978]

Hi Thanks for the reply

The 35 is an auto number field on the form that is loaded when I perform the append query.

The trouble is the criteria (RoleprofileID) for the query is already taken form a text box on the form. What happens is I want to create a new record that takes information from records 11,12 & 13 and creates an altogether new record (record number 14 or 35 as used in my example). So I select what records I want to append into a new record and that is used in the criteria. What I need is to know is how tell the append query take the RoleprofileID criteria from TextboxA but write the RoleprofileID value in textboxB into the tblRoleAssignments table under the column called RoleprofileID.

I thought I could write some VB code that when the command button is selected to run the append query it also looks at the table and replaces all fields matching "0" in the RoleprofileID column with the [Forms]![Roleprofileform].[RoleprofileID] of the loaded form but I don't know VB as well as I should and don't know how to tell it to do this. I am open to all idea however.

Hpefully this makes sense to you all.

 

[Pat Hartman]

The value you are appending can be different from the value you are selecting on. Open the query in SQL view if you can't figure out how to do it in design view.

Insert Into YourTable ApplicationrightID, RoleProfileID)
Select [Forms]![Roleprofileform].[textboxB], ApplicationrightID From YourTable
Where ApplicationrightID = [Forms]![Roleprofileform].[ApplicationrightID] AND RoleprofileID = [Forms]![Roleprofileform].[textboxA];

 

[iain1978]

Thanks for the reply Pat,

I think I'm nearly there, from the SQL you gave I created the below SQL which appears to work ok. But when I set the query to only select unique Values it does not work and I get the error message "Role profile set 9 field(s) to Null due to a type conversation failure... blah blah"

When I don't select unique values it woks just as I wanted although any duplicate entries are now added twice to the new role instead of once.

I'm presuming this will either be very easy to resolve or a complete fiddly nightmare, help as always appreciated.

INSERT INTO tblRoleAssignments ( RoleprofileID, ApplicationrightID )
SELECT DISTINCT [Forms]![Roleprofileform].[Role profile ID] AS Expr1, tblRoleAssignments.ApplicationrightID
FROM tblRoleAssignments
WHERE (((tblRoleAssignments.RoleprofileID)=[Forms]![Roleprofileform]![Combo62] Or (tblRoleAssignments.RoleprofileID)=[Forms]![Roleprofileform]![Combo64] Or (tblRoleAssignments.RoleprofileID)=[Forms]![Roleprofileform]![Combo65] Or (tblRoleAssignments.RoleprofileID)=[Forms]![Roleprofileform]![Combo66] Or (tblRoleAssignments.RoleprofileID)=[Forms]![Roleprofileform]![Combo67]));

 

[Pat Hartman]

The problem arises because of the multiple selection criteria. Essentially rather than copying ONE set of data, you are copying the union of MULTIPLE sets of data.